How to extract attribute value from XML in SQL Server 2019 (v15)?

I would need to extract elements from this XML into a tabular form, but I can’t seem to get my head around how this would work on SQL Server via something like XQuery.

I have all the data in a temporary table called “#1” and the XML itself lies in a field called “Message” in that temporary table. How can I extract the values “Test1” and “2,2 %” into separate fields called “W08003” and “W1A081”, respectively? The attribute names and the schema will remain the same over time? I would also need to do this on a row by row basis for each XML in the current temporary table.

<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Content>
    <status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
    <uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
      <xmlReply>
        <reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
          <d5p1:report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
            <d5p1:group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
              <d5p1:term d5p1:id="W08001">9760508923</d5p1:term>
              <d5p1:term d5p1:id="W08002">7605089277</d5p1:term>
              <d5p1:term d5p1:id="W08003">Test1</d5p1:term>
              <d5p1:term d5p1:id="W08004">Test2</d5p1:term>
            </d5p1:group>
            <d5p1:group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
              <d5p1:term d5p1:id="W1A003">000000000000000022</d5p1:term>
              <d5p1:term d5p1:id="W1A081">2,2 %</d5p1:term>
              <d5p1:term d5p1:id="W1A082">2,18839</d5p1:term>
            </d5p1:group>
          </d5p1:report>
        </reports>
      </xmlReply>
    </uc>
  </Content>
</Individual>

Current SQL code:

WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT ok.*
    ,X.g.value('(@d5p1:id)','varchar(20)') AS id
    ,X.g.value('(text())[1]','varchar(20)') AS term
into #2
FROM #1 as ok
CROSS APPLY(ok.[Message].nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g)

Answer

With no expected results, perhaps this is enough to get you started.

As you define a default namespace only once you get to status, you can’t use a DEFAULT namespace in XMLNAMESPACES, so I name it ns and reference that instead. This gives you the value of all the terms and their id attribute:

DECLARE @XML xml = '<Individual xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <Content>
        <status xmlns:d3p1="http://www.uc.se/schemas/ucOrderReply/" xmlns="http://www.uc.se/schemas/ucOrderReply/" d3p1:result="ok" />
        <uc xmlns="http://www.uc.se/schemas/ucOrderReply/">
            <xmlReply>
                <reports xmlns:d5p1="http://www.uc.se/schemas/ucOrderReply/" d5p1:lang="eng">
                    <report d5p1:id="7605089247" d5p1:name="Test1 Test2" d5p1:styp="K39" d5p1:index="0">
                        <group d5p1:id="W080" d5p1:index="0" d5p1:key="" d5p1:name="ID particulars">
                            <term d5p1:id="W08001">9760508923</term>
                            <term d5p1:id="W08002">7605089277</term>
                            <term d5p1:id="W08003">Test1</term>
                            <term d5p1:id="W08004">Test2</term>
                        </group>
                        <group d5p1:id="W1A0" d5p1:index="0" d5p1:key="" d5p1:name="UC RPB">
                            <term d5p1:id="W1A003">000000000000000022</term>
                            <term d5p1:id="W1A081">2,2 %</term>
                            <term d5p1:id="W1A082">2,18839</term>
                        </group>
                    </report>
                </reports>
            </xmlReply>
        </uc>
    </Content>
</Individual>';
WITH XMLNAMESPACES('http://www.uc.se/schemas/ucOrderReply/' AS ns,'http://www.uc.se/schemas/ucOrderReply/' AS d5p1)
SELECT X.g.value('(@d5p1:id)','varchar(20)') AS id,
       X.g.value('(text())[1]','varchar(20)') AS term
FROM @XML.nodes('Individual/Content/ns:uc/ns:xmlReply/ns:reports/ns:report/ns:group/ns:term') X(g);

I note that the XML has been changed since the initial version I used to write this answer. This answer has not been (read “won’t be”) adjusted for that.

Advertisement

Answer