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?

Answer 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.