I'm using SQL Server 2014 and aware that out of the box it does not support JSON.

We are receiving data from a 3rd party supplier that will look like the below:

{
  "PersonID": "1",
  "MarketingPreference": "Allow",
  "AllowPhone": "No",
  "AllowEmail": "Yes",
  "AllowTxt": "Yes",
  "AllowMob": "Yes"
}

However, we may sometimes also receive the below:

{
  "PersonID": "2",
  "MarketingPreference": "DoNotAllow"
}

I need to insert these values into a table - what is the best way to do this if SQL Server 2014 does not support JSON?

If I convert the JSON to XML it looks like the below:

<PersonID>1</PersonID>
<MarketingPreference>Allow</MarketingPreference>
<AllowPhone>No</AllowPhone>
<AllowEmail>Yes</AllowEmail>
<AllowTxt>Yes</AllowTxt>
<AllowMob>Yes</AllowMob>

How do I then extract the values from the XML?

DECLARE @xml XML
SET @xml = N'
<PersonID>1</PersonID>
<MarketingPreference>Allow</MarketingPreference>
<AllowPhone>No</AllowPhone>
<AllowEmail>Yes</AllowEmail>
<AllowTxt>Yes</AllowTxt>
<AllowMob>Yes</AllowMob>'
 
SELECT
    Tab.Col.value('@PersonID','int') AS ContactID,
    Tab.Col.value('@MarketingPreference','varchar(20)') AS Pref,
    Tab.Col.value('@AllowPhone','varchar(20)') AS Phone,
    Tab.Col.value('@AllowEmail','varchar(20)') AS Email,
    Tab.Col.value('@AllowTxt','varchar(20)') AS Txt,
    Tab.Col.value('@AllowMob','varchar(20)') AS Mob
FROM   
    @xml.nodes('/root/') Tab(Col)
GO;

But now I get this error:

Incorrect syntax near 'GO'.

Is there an easier way to select the values from JSON?

CodePudding user response：

You don't need a GO (never mind GO; which is not valid), and your XML syntax just seems to have been plucked from your first search result? Try:

SELECT PersonID   = x.p.value('(PersonID)[1]', 'int'),
       MarkPref   = x.p.value('(MarketingPreference)[1]', 'varchar(20)'),
       AllowPhone = x.p.value('(AllowPhone)[1]','varchar(20)'),
       AllowEmail = x.p.value('(AllowEmail)[1]','varchar(20)'),
       AllowTxt   = x.p.value('(AllowTxt)[1]',  'varchar(20)'),
       AllowMob   = x.p.value('(AllowMob)[1]',  'varchar(20)')
FROM @xml.nodes('.') AS x(p);

Output:

• Example db<>fiddle