How to get multiple nodes under 1 single node with T-SQL

Question

My xml file looks something like this:

<PackageRuntimeContext xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <UserToken>
<Id>449694</Id>
  </UserToken>
  <Addresses>
        <Address>
      <LastSeen xsi:nil="true" />
      <UniqueID>9afd29f6-f4fe-4a91-aade-da8a3fcdc358</UniqueID>
      <IsPrimary>true</IsPrimary>
      <Id>0</Id>
      <OrderID>0</OrderID>
      <SubjectId>0</SubjectId>
      <AddressLine1>123 Main St.</AddressLine1>
      <City>louisville</City>
      <State>KY</State>
      <ZipCode>40206</ZipCode>
    </Address>
    <Address>
      <LastSeen xsi:nil="true" />
      <UniqueID>0ae8014e-a950-48f3-8ee6-3526a7f3a50d</UniqueID>
      <IsPrimary>true</IsPrimary>
      <Id>0</Id>
      <OrderID>0</OrderID>
      <SubjectId>0</SubjectId>
      <AddressLine1>789 Elm St.</AddressLine1>
      <City>louisville</City>
      <State>KY</State>
      <ZipCode>40206</ZipCode>
    </Address>
    <Address>
      <LastSeen xsi:nil="true" />
      <UniqueID>b1bcc271-bec8-432f-b968-25430ba63b95</UniqueID>
      <IsPrimary>false</IsPrimary>
      <Id>0</Id>
      <OrderID>0</OrderID>
      <SubjectId>0</SubjectId>
      <AddressLine1>456 Oak St.</AddressLine1>
      <City>louisville</City>
      <State>KY</State>
      <ZipCode>40206</ZipCode>
    </Address>
  </Addresses>

I want to get the <Id> number 449694, and with it, the 3 (or whatever) subsequent <UniqueID> numbers under Addresses/Address so it looks something like this:

IDNumber      UniqueID
========      ========
449694         9afd29f6-f4fe-4a91-aade-da8a3fcdc358
449694         0ae8014e-a950-48f3-8ee6-3526a7f3a50d
449694         b1bcc271-bec8-432f-b968-25430ba63b95

The code If found here (How to query values from xml nodes?) directed me to write something like this:

SELECT 
t.p.value('(./UserToken/Id)[1]', 'int') [IdNumber],
t.p.value('(./Addresses/Address/UniqueID)[1]', 'varchar(max)') [Context]
FROM product.PackageRuntimeState prs WITH(NOLOCK)
CROSS APPLY prs.Context.nodes('/PackageRuntimeContext') t(p)

My results were:

IDNumber      UniqueID
========      ========
449694        9afd29f6-f4fe-4a91-aade-da8a3fcdc358
449694        b8439471-d4b9-46db-9321-b6175e1b8fb4  (this is from ANOTHER record)
449694        b8439471-d4b9-46db-9321-b6175e1b8fb4  (this too is from another record)

What do I need to do to my code to get the subsequent UniqueID nodes from my xml file?

Thanks!

Anon · Accepted Answer · 2014-01-16T20:04:40.967

1

Drop down one more level. You need to list the direct decendants of <Addresses>, not <PackageRuntimeContext>

SELECT
  t.p.value('(../../UserToken/Id)[1]', 'int') [IdNumber],
  t.p.value('(./UniqueID)[1]', 'varchar(max)') [Context]
FROM product.PackageRuntimeState prs WITH(NOLOCK)
CROSS APPLY prs.Context.nodes('/PackageRuntimeContext/Addresses/Address') t(p)

edited Jan 16 '14 at 20:04

answered Jan 16 '14 at 19:49

Anon

10,660
1
29
31

When I try running the code as suggested I get "NULL" now in my "Context" field. Probably because the full path to get to the UniqueID node is "PackageRuntimeContext/Addresses/Address/UniqueID" and the element "Address" is not being applied. I tried t.p.value('(./Address/UniqueID)[1]', 'varchar(max') Context and that got me back to my original results – John Waclawski Jan 16 '14 at 20:01

How to get multiple nodes under 1 single node with T-SQL

1 Answers1