How to read a xml column in SqlServer

Question

I have an XML column in SqlServer table and I'm supposed to query few values from the XML.

My XML column resembles @str variable below:

DECLARE @str nvarchar(2000)
SET @str = '<EmpHists>
  <EmpHist> seqNb="1" orgNm="ABC Corporation" employed="N" city="SACRAMENTO" state="CA" cntryCd="USA" pstnHeld="OTHER - OFFICE MANAGER">
    <DtRng fromDt="1981-03" />
  </EmpHist>
    <EmpHist> seqNb="2" orgNm="DEF Corporation" employed="Y" city="NEWYORK" state="NY" cntryCd="USA" pstnHeld="OTHER - OFFICE STAFF">
    <DtRng fromDt="1981-03" />
  </EmpHist>
    <EmpHist> seqNb="3" orgNm="GHI Corporation" employed="N" city="LOSANGELS" state="LA" cntryCd="USA" pstnHeld="OTHER - OFFICE ASSISTANT">
    <DtRng fromDt="1981-03" />
  </EmpHist>
</EmpHists>'

I have tried using below query to read the seqNb and orgNm values with no results.

DECLARE @xml xml
SELECT @xml = CAST(CAST(@str AS VARBINARY(MAX)) AS XML) 
Select
     m.c.value('@seqNb', 'int') as SequenceNumber1,
     m.c.query('./seqNb').value('.','int') as SequenceNumber2,
     m.c.value('@orgNm', 'varchar(max)') as OrganizationName1,
     m.c.query('./orgNm').value('.','int') as OrganizationName2
from @xml.nodes('/EmpHists/EmpHist') as m(c)

I have tried to replicate the query from: How to query for Xml values and attributes from table in SQL Server?

But I am not able to get the results due to some differences in the XML data compared to others I've looked at (like the DtRng tag). What am I missing?

Why are you using an XML field instead of a separate table in the first place? What you showed is normal tabular data stored as XML. That takes more space than a normal table and results in slow performance because fields can't really be indexed. — Panagiotis Kanavos, Aug 07 '23 at 11:02
The SQL is valid, @PanagiotisKanavos ; it won't return the data the OP wants, but it is valid. The "XML" is the problem, not the SQL. — Thom A, Aug 07 '23 at 11:05
It seems you used the wrong answer from the linked question. The double cast is pointless because that table's field is already an `xml` field. The answer itself is identical to those using APPLY, once you remove the double cast — Panagiotis Kanavos, Aug 07 '23 at 11:09
And I suppose the XML is "valid" - but `seqNb="1" orgNm="ABC Corporation" employed="N" city="SACRAMENTO" state="CA" cntryCd="USA" pstnHeld="OTHER - OFFICE MANAGER"` is just a `text()` node so not queryable with XML methods. And seems to be an error as these were presumably intended to be attributes of `EmpHist` — Martin Smith, Aug 07 '23 at 11:10
` seqNb="1" ` isn't valid. It would have to be surrounded with `CDATA` — Panagiotis Kanavos, Aug 07 '23 at 11:11
It's valid as far as SQL Server is concerned https://dbfiddle.uk/FtlDZxgT — Martin Smith, Aug 07 '23 at 11:12
@MartinSmith, is right. The XML should be changed. Thank you :) Please post that comment as answer if you would want it as accepted answer. — Arun, Aug 07 '23 at 11:14

score 1 · Answer 1 · answered Aug 07 '23 at 11:12

Your XML is not correct: the attributes have not been encoded correctly and are instead embedded in the text of the node.

You can mangle it back into shape like this

Select
     x2.EmpHist.value('@seqNb', 'int') as SequenceNumber1,
     x2.EmpHist.value('@orgNm', 'varchar(max)') as OrganizationName1
FROM YourTable t
CROSS APPLY t.xmlData.nodes('/EmpHists/EmpHist') as x1(EmpHist)
CROSS APPLY (
    SELECT
      CAST(CONCAT('<EmpHist ', x1.EmpHist.value('text()[1]', 'nvarchar(max)'), '</EmpHist>') AS xml)
) v(Mangled)
CROSS APPLY v.Mangled.nodes('EmpHist') as x2(EmpHist);

db<>fiddle

Basically what this does is pull out the inner text value, and concat onto it a begin and end tag, then cast it back to XML.

How to read a xml column in SqlServer

1 Answers1