Parsing XML in a SQL Table

Question

I've done some research on this topic on this site and elsewhere, and I think I'm somewhat close to a solution, but I'm still having some trouble. I have XML saved in a record that, when formatted, looks like this (This is a snippet):

<samlp:Response>
<saml:Assertion>
<saml:AttributeStatement>
     <saml:Attribute Name="Market Indicator">
        <saml:AttributeValue>SG</saml:AttributeValue>
     </saml:Attribute>
     <saml:Attribute Name="Proposed Coverage Effective Date">
        <saml:AttributeValue>12/1/2017</saml:AttributeValue>
     </saml:Attribute>
     <saml:Attribute Name="Premium Amount Total">
        <saml:AttributeValue>5.00</saml:AttributeValue>
     </saml:Attribute>
     <saml:Attribute Name="Group Identifier">
        <saml:AttributeValue>11111</saml:AttributeValue>
     </saml:Attribute>
     <saml:Attribute Name="Group Name">
        <saml:AttributeValue>Construction Company</saml:AttributeValue>
     </saml:Attribute>
     </saml:Attribute>
     <saml:Attribute Name="State">
        <saml:AttributeValue>PA</saml:AttributeValue>
     </saml:Attribute>
</saml:AttributeStatement>
</saml:Assertion>
</samlp:Response>

The first problem I am having is being able to call out atttribute names with spaces:

SELECT m.c.value('@Group Identifier', 'varchar(max)') AS [Group]
FROM dbo.PaymentLog PL
outer apply 
pl.response.nodes('/Response/Assertion/AttributeStatement/Attribute') as 
m(c)


Msg 2209, Level 16, State 1, Line 84
XQuery [dbo.PaymentLog.Response.value()]: Syntax error near 'Group'

But if I try an attribute name without a space, I just get NULL results.

SELECT m.c.value('@State', 'varchar(max)') AS [State]
FROM dbo.PaymentLog PL
outer apply 
pl.response.nodes('/Response/Assertion/AttributeStatement/Attribute') as 
m(c)

I've tried the below query also, but also get NULL results:

SELECT m.c.value('data(.)', 'varchar(max)') AS [Values]
FROM dbo.PaymentLog PL
outer apply 
pl.response.nodes('/Response/Assertion/AttributeStatement/Attribute
[@Name="State"]') as m(c)

I think I'm in the ballpark, but nothing I can find seems to address the specific schema of my XML.

Dealing with namespaces in XML can be confusing. It looks like your XML is incomplete (you have two different namespaces but you haven't shown what the namespaces actually point to), but one solution for you might be something like `with xmlnamespaces ('abc.com' as samlp, 'xyz.com' as saml) select m.c.value... pl.response.nodes('/samlp:Response/saml:Assertion/saml:AttributeStatement/saml:Attribute[@Name="State"]') as m(c)` where abc.com and xyz.com are whatever your namespaces actually are. — ZLK, Apr 17 '18 at 22:10

Shnugo · Answer 1 · 2018-04-18T09:39:12.893

There are several things to say:

Your XML snippet uses namespace prefixes (saml: and samlp:). This is not just some fancy extra, but must be declared or wildcarded
Showing a snippet is a good idea (keep it short and simple), but - not knowing the container nodes - it is hard to provide the best answer
Using the name saml:Attribute is bewildering, as this is an XML element actually and not an attribute. There is an attribute called Name which has different values
This XML is a EAV structure. EAVs are - in most cases - not the best idea... If this under your control you might think about changing this...
This XML mixes data types without any hint, what type to expect. So your schema must know in advance how to read this...
The date format 12/1/2017 is localized and depending on your systems culture. Some systems will take the 1st of Dec, others will read the 12th of Jan. Always use ISO8601 for this! This might work perfectly on your machines, but breaks with silly errors on a customer's system.
Such a structure might come with more or with different fields the next time. Your reader must be very generic...

About your code
You try to read SELECT m.c.value('@Group Identifier', 'varchar(max)') AS [Group]. You are mixing the attribute's value and the attribute's name

Same here:

But if I try an attribute name without a space, I just get NULL results. SELECT m.c.value('@State', 'varchar(max)') AS [State]

No, the attribute's name is Name, while its value is State. You'd use a predicate here (see my code)

Your last attempt points to the right direction, but as <Response> is (assumably) not the top node, you will not find it with /Response. Further more you are missing the namespaces.

Try it like this:

DECLARE @mockupTable TABLE(id INT IDENTITY, response XML);
INSERT INTO @mockupTable VALUES
(N'<root xmlns:saml="blah1" xmlns:samlp="blah2">
  <samlp:Response>
    <saml:Assertion>
      <saml:AttributeStatement>
        <saml:Attribute Name="Market Indicator">
          <saml:AttributeValue>SG</saml:AttributeValue>
        </saml:Attribute>
        <saml:Attribute Name="Proposed Coverage Effective Date">
          <saml:AttributeValue>12/1/2017</saml:AttributeValue>
        </saml:Attribute>
        <saml:Attribute Name="Premium Amount Total">
          <saml:AttributeValue>5.00</saml:AttributeValue>
        </saml:Attribute>
        <saml:Attribute Name="Group Identifier">
          <saml:AttributeValue>11111</saml:AttributeValue>
        </saml:Attribute>
        <saml:Attribute Name="Group Name">
          <saml:AttributeValue>Construction Company</saml:AttributeValue>
        </saml:Attribute>
        <saml:Attribute Name="State">
          <saml:AttributeValue>PA</saml:AttributeValue>
        </saml:Attribute>
      </saml:AttributeStatement>
    </saml:Assertion>
  </samlp:Response>
</root>');

--This query uses WITH XMLNAMESPACES to declare the namespaces properly (recommended!). Use the same values as given in the xml within xmlns:saml="here" instead of blah.
--I use the deep search with two slashes //Response, as I do not know, where (how deep) this is nested.
--I use .nodes() down to <saml:Attribute> to read your values as EAV list.

WITH XMLNAMESPACES('blah1' AS saml
                  ,'blah2' AS samlp)
SELECT attr.value(N'@Name','nvarchar(max)') AS AttributeName
      ,attr.value(N'(saml:AttributeValue/text())[1]','nvarchar(max)') AS AttributeValue
FROM @mockupTable PL
OUTER APPLY PL.response.nodes('//samlp:Response/saml:Assertion/saml:AttributeStatement/saml:Attribute') AS A(attr)

--This query uses namespace-wildcards (*: - not recommended)
--I use predicates to get your values typed in one row
--But you must know all expected values in advance

SELECT attr.value(N'(*:Attribute[@Name="Market Indicator"]/*:AttributeValue/text())[1]','nvarchar(max)') AS [Market Indicator]
      ,attr.value(N'(*:Attribute[@Name="Proposed Coverage Effective Date"]/*:AttributeValue/text())[1]','nvarchar(max)') AS [Proposed Coverage Effective Date]
      ,attr.value(N'(*:Attribute[@Name="Premium Amount Total"]/*:AttributeValue/text())[1]','decimal(10,4)') AS [Premium Amount Total]
      ,attr.value(N'(*:Attribute[@Name="Group Identifier"]/*:AttributeValue/text())[1]','bigint') AS [Group Identifier]
      ,attr.value(N'(*:Attribute[@Name="Group Name"]/*:AttributeValue/text())[1]','nvarchar(max)') AS [Group Name]
      ,attr.value(N'(*:Attribute[@Name="State"]/*:AttributeValue/text())[1]','nvarchar(max)') AS [State]
FROM @mockupTable PL
OUTER APPLY PL.response.nodes('//*:Response/*:Assertion/*:AttributeStatement') AS A(attr)

Thank you for your thorough answer! I understand the syntax that you suggest I use, but in my case, I am pulling the xml from a field in a table. So instead of inserting one xml string, I want to pull it from the table, like: `DECLARE @xml XML SET @xml = (SELECT response FROM dbo.PaymentLog PL WHERE PL.Request = 'Tuo')` — Scott Ferguson, Apr 26 '18 at 17:32
`WITH XMLNAMESPACES('?' AS saml, '?' AS samlp) SELECT b.value(N'@Name','nvarchar(max)') AS AttributeName, b.value(N'(saml:AttributeValue/text())[1]','nvarchar(max)') AS AttributeValue FROM @xml.nodes('//samlp:Response/saml:Assertion/saml:AttributeStatement/saml:Attribute') as a(b)` I'm confused on what my namespaces would be in this scenario. — Scott Ferguson, Apr 26 '18 at 17:32

Parsing XML in a SQL Table

1 Answers1