XML query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

XML query question

Author

Topic

XMLNewbie7
Starting Member

4 Posts

Posted - 2011-11-18 : 19:12:35

Here is my simple example:

---------------------------------------------------------


declare @x xml;
set @x = '
<Main>
  <Parent Name="John Smith">
	  <Occupation Name="CEO" />
	  <ChildRef ID="111111" />
	  <ChildRef ID="222222" />
  </Parent>
  <Parent Name="Tom Jones">
	  <Occupation Name="VP" />
  </Parent>
  <Child ID="111111" Name="Tim">
  </Child>
  <Child ID="222222" Name="Jen">
  </Child>
  <Child ID="333333" Name="Ken">
  </Child>
</Main>
';

select 
   Parent.a.value('@Name','varchar(255)') [Parent Name]
  ,ChildRef.b.value('@ID','varchar(255)') [Child ID]
from @x.nodes('/Main/Parent') Parent(a) 
outer apply Parent.a.nodes('ChildRef') ChildRef(b)
;

---------------------------------------------------------

It gives this result:

Parent Name Child ID
----------- -------- ----------
John Smith 111111
John Smith 222222
Tom Jones NULL

---------------------------------------------------------

My question is: How can the query be modified to get this result:

Parent Name Child ID Child Name
----------- -------- ----------
John Smith 111111 Tim
John Smith 222222 Jen
Tom Jones NULL NULL

Seems simple enough, but I've struggled with it. Can anyone please help?

Thank you!

XMLNewbie7
Starting Member

4 Posts

Posted - 2011-11-18 : 19:23:23

Here is a query that I've tried, but it returns undesired results when a Parent has no children:


select 
   Parent.a.value('@Name','varchar(255)') [Parent Name]
  ,ChildRef.b.value('@ID','varchar(255)') [Child ID]
  ,Child.c.value('@Name','varchar(255)') [Child Name]
from @x.nodes('/Main/Parent') Parent(a) 
outer apply Parent.a.nodes('ChildRef') ChildRef(b)
outer apply @x.nodes('/Main/Child') Child(c)
where ChildRef.b.value('@ID','varchar(255)') = Child.c.value('@ID','varchar(255)')
or ChildRef.b.value('@ID','varchar(255)') is null
;

The results it gives is:

Parent Name	Child ID	Child Name
John Smith	111111	Tim
John Smith	222222	Jen
Tom Jones	NULL	Tim
Tom Jones	NULL	Jen
Tom Jones	NULL	Ken

XMLNewbie7
Starting Member

4 Posts

Posted - 2011-11-18 : 19:32:03

I FOUND THE ANSWER!

Here is the query that gets the right results:

select 
   Parent.a.value('@Name','varchar(255)') [Parent Name]
  ,ChildRef.b.value('@ID','varchar(255)') [Child ID]
  ,Child.c.value('@Name','varchar(255)') [Child Name]
from @x.nodes('/Main/Parent') Parent(a) 
outer apply Parent.a.nodes('ChildRef') ChildRef(b)
left outer join @x.nodes('/Main/Child') Child(c)
on ChildRef.b.value('@ID','varchar(255)') = Child.c.value('@ID','varchar(255)')
;

It returns this result:

Parent Name	Child ID	Child Name
John Smith	111111	Tim
John Smith	222222	Jen
Tom Jones	NULL	NULL

I'm doing a happy dance now, because it took a few hours to finally get it!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-19 : 03:33:27

another way, without using intermediate join


declare @x xml;
set @x = '
<Main>
  <Parent Name="John Smith">
	  <Occupation Name="CEO" />
	  <ChildRef ID="111111" />
	  <ChildRef ID="222222" />
  </Parent>
  <Parent Name="Tom Jones">
	  <Occupation Name="VP" />
  </Parent>
  <Child ID="111111" Name="Tim">
  </Child>
  <Child ID="222222" Name="Jen">
  </Child>
  <Child ID="333333" Name="Ken">
  </Child>
</Main>
';
select [Parent Name],[Child_ID]
,m.n.value('@Name','varchar(200)') AS Child_Name
from
(
select 
   Parent.p.value('@Name','varchar(255)') [Parent Name]
  ,t.u.value('@ID','varchar(255)') [Child_ID]
  ,Main.m.query('./Child') AS Child
from @x.nodes('/Main') Main(m)
cross apply Main.m.nodes('Parent') Parent(p) 
outer apply Parent.p.nodes('ChildRef') t(u)
)t
outer apply Child.nodes('Child[@ID = sql:column("Child_ID")]')m(n)

output
-----------------------------------------------------
Parent Name	Child_ID	Child_Name
John Smith	111111	        Tim
John Smith	222222	        Jen
Tom Jones	NULL	        NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

XMLNewbie7
Starting Member

4 Posts

Posted - 2011-11-21 : 13:42:03

OK - thank you for showing another way of getting the results - I appreciate the info!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-22 : 01:14:55

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources