|
Home > Archive > MS SQL XML > October 2005 > Need help in merging two xml Columns for querying (SQL Server 2005)
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Need help in merging two xml Columns for querying (SQL Server 2005)
|
|
| honsremind@gmail.com 2005-09-28, 8:24 pm |
| Hi all,
Please consider the following problem:
I have created two tables as follows
create table tree(id int, tree xml, PRIMARY KEY (id))
create table props(ref int, property xml, PRIMARY KEY (ref))
I would like to take an xml entry from table tree, say:
--- tree table (1st row) ---
<tree>
<node id='1'>
<property ref='1'/>
<property ref='3'/>
<node id='2'>
<property ref='2'/>
</node>
</node>
</tree>
and merge this xml entry with xml entries from table props:
---- props table ----
1 | <properties><p>abc</p><p>bcd</p></properties>
2 | <properties><p>abd</p><p>bbb</p></properties>
3 | <properties><p>ggg</p><p>vvv</p></properties>
4 | <properties><p>ddd</p><p>ddd</p></properties>
To Get:
<tree>
<node id='1'>
<properties><p>abc</p><p>bcd</p></properties>
<properties><p>ggg</p><p>vvv</p></properties>
<node id ='2'>
<properties><p>abd</p><p>bbb</p></properties>
</node>
</node>
</tree>
Then run a query on the contents to retrieve the id(s) of the node(s)
with property bbb (Answer is 2):
ie. xpath query: //@id[properties/p='bbb']
Being new to XML and SQL Server 2005, I'm finding it rather challenging
to do this. I don't want to create a new XML node, rather i want to
join the xml data for a query and return a result. Any help would be
much appreciated. Thank you,
Dennis
| |
| Michael Rys [MSFT] 2005-10-01, 8:23 pm |
| In order to give you a good answer, I would like to know, whether you need
the intermediate tree or only the final result of the nodes with the ids for
the given property value.
In the first, case, I have a partial solution that does not preserve the
structure directly:
select R1.node.value('@id', 'int') as "@id",
(select p.property as "*"
from R1.node.nodes('property') R2(prop)
join props p on 1=R2.prop.exist('@ref[.=sql:column("p.ref")]'
)
for xml path(''), type)
from tree t cross apply t.tree.nodes('//node') R1(node)
for xml path('node'), root('tree'), type
You could extend it with some more information and then use XSLT to
generate the tree. However, if all you want to find is the node id without
worrying about the structure, the following can do the trick:
create function findnode(@v varchar(50))
returns table
as return
select R1.node.value('@id', 'int') as nodeid
from tree t cross apply t.tree.nodes('//node') R1(node)
cross apply R1.node.nodes('property') R2(prop)
join props p on 1=R2.prop.exist('@ref[.=sql:column("p.ref")]')
and 1= p.property.exist('/properties/p[.=sql:variable("@v")]')
go
select * from findnode('bbb')
I hope this helps
Michael
<honsremind@gmail.com> wrote in message
news:1127955616.967923.216520@g44g2000cwa.googlegroups.com...
> Hi all,
>
> Please consider the following problem:
>
> I have created two tables as follows
>
> create table tree(id int, tree xml, PRIMARY KEY (id))
> create table props(ref int, property xml, PRIMARY KEY (ref))
>
> I would like to take an xml entry from table tree, say:
> --- tree table (1st row) ---
> <tree>
> <node id='1'>
> <property ref='1'/>
> <property ref='3'/>
> <node id='2'>
> <property ref='2'/>
> </node>
> </node>
> </tree>
>
> and merge this xml entry with xml entries from table props:
> ---- props table ----
> 1 | <properties><p>abc</p><p>bcd</p></properties>
> 2 | <properties><p>abd</p><p>bbb</p></properties>
> 3 | <properties><p>ggg</p><p>vvv</p></properties>
> 4 | <properties><p>ddd</p><p>ddd</p></properties>
>
>
> To Get:
> <tree>
> <node id='1'>
> <properties><p>abc</p><p>bcd</p></properties>
> <properties><p>ggg</p><p>vvv</p></properties>
> <node id ='2'>
> <properties><p>abd</p><p>bbb</p></properties>
> </node>
> </node>
> </tree>
>
> Then run a query on the contents to retrieve the id(s) of the node(s)
> with property bbb (Answer is 2):
> ie. xpath query: //@id[properties/p='bbb']
>
> Being new to XML and SQL Server 2005, I'm finding it rather challenging
> to do this. I don't want to create a new XML node, rather i want to
> join the xml data for a query and return a result. Any help would be
> much appreciated. Thank you,
>
> Dennis
>
|
|
|
|
|