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
– Panagiotis Kanavos, Commented Aug 7, 2023 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
– Thom A ♦, Commented Aug 7, 2023 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
– Panagiotis Kanavos, Commented Aug 7, 2023 at 11:09
It's valid as far as SQL Server is concerned dbfiddle.uk/FtlDZxgT — Martin Smith
– Martin Smith, Commented Aug 7, 2023 at 11:12

Charlieface · Accepted Answer · 2023-08-07 11:12:50Z

1

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.

answered Aug 7, 2023 at 11:12

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to read a xml column in SqlServer

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related