SQL Server 2014 JSON into table

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?

1 answer

  • answered 2022-05-04 12:22 Aaron Bertrand

    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:

    PersonID MarkPref AllowPhone AllowEmail AllowTxt AllowMob
    1 Allow No Yes Yes Yes

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum