CODEWITHSUNDEEP
sqlxmlsql-server-2008t-sql
vr8ce
vr8ce

Reputation: 544

Querying XML that has multiple sub-entities

I have a basic understanding of how to query XML in SQL Server 2008+. Basic. But I am struggling to get what I want from this simple XML, and I've searched SO and the web at large for quite a while this afternoon and haven't hit upon the magic words to find help.

Given this XML:

declare @xml xml =
'<?xml version="1.0" encoding="utf-8"?>
<Customers>
  <Customer id="12345" name="John Doe" addr1="123 Somewhere Ln" city="Riverside" state="CA">
    <Children>Jane</Children>
    <Children>John Jr.</Children>
    <Children>Susan</Children>
  </Customer>
</Customers>
<Customers>
  <Customer id="97531" name="Ben Franklin" addr1="456 Anywhere Dr" city="Albuquerque" state="NM">
    <Children>Andrew</Children>
    <Children>Adam</Children>
  </Customer>
</Customers>'

I can run this query and get the ids and names:

select cust.col.value('@id[1]','int') as id,
       cust.col.value('@name[1]','varchar(30)') as name
  from @xml.nodes('/Customers/Customer') as cust(col)

id     name
12345  John Doe
97531  Ben Franklin

However, what I want next is the id and name of each child, and I have not been able to figure out what's needed to get that. IOW, I want this:

id      child
12345   Jane
12345   John Jr.
12345   Susan
97531   Andrew
97531   Adam

Note that I want columnar data, not XML.

So, what is the SELECT that will give me those results from that xml?

Thanks!

Upvotes: 3

Views: 53

Answers (2)

gofr1
gofr1

Reputation: 15987

You can just:

select cust.col.value('../@id','int') as id,
       cust.col.value('../@name','varchar(30)') as name,
       cust.col.value('.','varchar(30)') as child
  from @xml.nodes('/Customers/Customer/Children') as cust(col)

to get:

id      name            child
12345   John Doe        Jane
12345   John Doe        John Jr.
12345   John Doe        Susan
97531   Ben Franklin    Andrew
97531   Ben Franklin    Adam

Upvotes: 1

har07
har07

Reputation: 89305

Building on your query, simply add CROSS APPLY on Children elements. Now you can select data from Customer as well as Children elements :

select cust.col.value('@id','int') as id,
       child.col.value('.','varchar(30)') as child
from @xml.nodes('/Customers/Customer') as cust(col)
    cross apply cust.col.nodes('Children') as child(col)

output :

id          child
----------- ------------------------------
12345       Jane
12345       John Jr.
12345       Susan
97531       Andrew
97531       Adam

Notice that . is used to reference current element context; and, in XML, there can't be 2 attributes of the same name in the same parent element, so indexer in @id[1] is not needed.

Upvotes: 1

Related Questions