Example: Unioning XQueries results from Variable instances
There was a post this morning the SQL Server 2005 XML newsgroup that caught my eye. I don't know that I answered the posters question exactly, but it lead to what I thought was an interesting example. Basically, it seemed to me that the poster had two variables -- each untyped XML -- and wanted to perform the same query on them and return a single XML instance. Here's how you might do that
-- Declare two untyped XML variables to use.
declare @x1 xml
declare @x2 xml
-- Populate them with data
set @x1=
N'<?xml version="1.0" encoding="UTF-16" ?>
<tree>
<height scale="meters">9</height>
<age scale="years">75</age>
<cn>Larch</cn>
</tree>'
set @x2=
N'<?xml version="1.0" encoding="UTF-16" ?>
<tree>
<height scale="meters">12</height>
<age scale="years">60</age>
<cn>Oak</cn>
</tree>'
-- Query both instances for the
-- common name of the tree
-- and return that as a sequence
-- of node named "type."
-- the date($a) returns the cn
-- element's inner text in this case
-- we need to use union all since
-- sequences constructed by query
-- aren't "distinctable" as far as
-- the T-SQL query process goes.
select @x1.query(
'for $a in (/tree/cn)
return (<type>{data($a)}</type>)'
)
union all
select @x2.query(
'for $a in (/tree/cn)
return (<type>{data($a)}</type>)'
)
-- We can then create a tree element
-- and put them in a "forest"
for xml path('tree'),root('forest'),type
Of course, this is tons easier to do if you already have the data in a table:
-- replace with a suitably named db for you.
use scratch
go
-- create a table to hold our tree documents
create table dbo.Forest(
pkid tinyint identity(1,1) primary key,
tree xml)
go
-- Insert some tree data
insert into forest(tree) values (
N'<?xml version="1.0" encoding="UTF-16" ?>
<tree>
<height scale="meters">9</height>
<age scale="years">75</age>
<cn>Larch</cn>
</tree>')
go
insert into forest(tree) values (
N'<?xml version="1.0" encoding="UTF-16" ?>
<tree>
<height scale="meters">12</height>
<age scale="years">60</age>
<cn>Oak</cn>
</tree>')
go
-- Query that out.
select tree.query(
'for $a in (/tree/cn)
return (<type>{data($a)}</type>)'
)
from forest
for xml path('tree'),root('forest'),type