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 TESTI 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
)