Loop through XML using SQL

Question

I have this XML in a column in a MSSQL table

<QueryXMLData>
  <main ShowAllPhoneNumbers="yes">
    <C ID="5753768" HID="1" Name="Michael" SSN="xxxxxxxxxxx" PayType="" Status="Active" StatusID="1">
      <S ID="5483911" HID="3" Name="Ethan" SSN="xxxxxxxxxxx" CType="Subscription" TPID="21456" TPName="Outside" TPShortName="Out" Status="Active" StatusID="P" D="Y" Checked="yes" Found="yes">
        <TPIDs>
          <Phone TP="1122334455" />
        </TPIDs>
      </S>
    </C>
    <C ID="5670554" HID="1" Name="Susan" SSN="xxxxxxxxxxx" PayType="" Status="Active" StatusID="1">
      <S ID="5297452" HID="3" Name="Johnathan" SSN="xxxxxxxxxxx" CType="Outbound" TPID="110" TPName="Out" TPShortName="Inside" Status="Active" StatusID="1" D="Y" Checked="yes" Found="yes">
        <TPIDs>
          <Phone TP="3344556677" />
        </TPIDs>
      </S>
      <S ID="5297426" HID="3" Name="Brad" SSN="xxxxxxxxxxx" CType="Outbound" TPID="110" TPName="Out" TPShortName="Inside" Status="Active" StatusID="1" D="Y" Checked="yes" Found="yes">
        <TPIDs>
          <Phone TP="5566778899" />
        </TPIDs>
      </S>
    </C>
  </main>
</QueryXMLData>

I would like for it to return these columns:

Customer Name     Subscriber Name        TPID    Phone TP
--------------------------------------------------------------
Michael           Ethan                  21456   1122334455
Susan             Johnathan                110   3344556677
Susan             Brad                     110   5566778899

I have tried

SELECT value.value('(QueryXMLData/main/C/@SSN)[1]', 'varchar(50)') AS Customer1,
value.value('(QueryXMLData/main/C/S/@SSN)[1]', 'varchar(50)') AS Subb1
from #tmp

But I would like for it to loop through every value instead of me inserting which line it is supposed to extract from.

@MartinSmith I've updated the columns on which I would like the query to return — theOGloc
– theOGloc, Commented Feb 13, 2019 at 20:13

Martin Smith · Accepted Answer · 2019-02-13 20:44:06Z

2

Another way to skin the cat...

SELECT
       n.value('../@Name', 'varchar(50)'),
       n.value('@Name', 'varchar(50)'),
       n.value('@TPID', 'varchar(50)'),
       n.value('(TPIDs/Phone/@TP)[1]', 'varchar(50)')
from #tmp
CROSS APPLY value.nodes('QueryXMLData/main/C/S') n(n)

Demo

answered Feb 13, 2019 at 20:44

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

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

1 Comment

Martin Smith Over a year ago

(no cats were harmed in the making of this answer)

John Cappelletti · Accepted Answer · 2019-02-13 20:43:37Z

Perhaps this will help

Select CustName = x1.value('@Name','varchar(150)')
      ,SubsName = x2.value('@Name','varchar(150)')
      ,TPID     = x2.value('@TPID','varchar(150)')
      ,PhoneTP  = x2.value('TPIDs[1]/Phone[1]/@TP','varchar(150)')
 From  @XML.nodes('/QueryXMLData/main/*') lv1 (x1)
 Cross Apply x1.nodes('*') lvl2 (x2)

Returns

CustName    SubsName    TPID    PhoneTP
Michael     Ethan       21456   1122334455
Susan       Johnathan   110     3344556677
Susan       Brad        110     5566778899

Now, If your data is in a table ... lets assume a column called XMLData

Example

Select A.ID
      ,B.*
 From  YourTable A
 Cross Apply (
                Select CustName = x1.value('@Name','varchar(150)')
                      ,SubsName = x2.value('@Name','varchar(150)')
                      ,TPID     = x2.value('@TPID','varchar(150)')
                      ,PhoneTP  = x2.value('TPIDs[1]/Phone[1]/@TP','varchar(150)')
                 From  XMLData.nodes('/QueryXMLData/main/*') lv1 (x1)
                 Cross Apply x1.nodes('*') lvl2 (x2)
             ) B

Collectives™ on Stack Overflow

Loop through XML using SQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related