Nested JSON instead on XML

Hi

I have some SQL that retreives data from one row of a table - currently in XML

AccountNumber CS198318 Ref1 3557269 Ref2 AutoReplenish Ref3 SiteId FR115 HP-A7109A 1 20424767 PICK TEST

I now need this is JSON

i’m struggling with the reference section

[{“Reference”:{“RefId”:“Ref1”,“RefValue”:“3557269”,“RefId2”:“Ref2”,“RefValue2”:“AutoReplenish”,“RefId3”:“Ref3”,“RefValue3”:""}}],

i get the above using the sql below. how can i replicate this as JSON
SELECT --Account
(
SELECT ‘AccountNumber’ as AccountIdentifier,
COALESCE(oh.[CourierAccount],con.AccountNumber) as Value
FOR XML PATH (’’), TYPE, ELEMENTS
) as [Account],

			--References
			convert(xml,
				'<Reference><RefId>Ref1</RefId><RefValue>' + convert(varchar,@ConsignmentID) + '</RefValue></Reference>' +
				'<Reference><RefId>Ref2</RefId><RefValue>' + isnull(replace(replace(replace(oh.customer_po,'<',' '),'>',' '),'&','+'),'') + '</RefValue></Reference>' +
				'<Reference><RefId>Ref3</RefId><RefValue>' + [dbo].RemoveInvalidXMLCharacters(isnull(oh.cust_ref,'')) + '</RefValue></Reference>'
				) as [References],
			
	FROM	Cons_Consignments			con
			JOIN order_h				oh	ON	oh.order_num = con.Reference
			JOIN country				cnt	ON	cnt.country_desc = con.Add_Country			--Country we're shipping to
	WHERE	con.IDX = @ConsignmentID
	FOR XML PATH('PickAdd'), TYPE, ELEMENTS	
	)
1 Like