I was wondering how someone would go about joining an XML fragment with a SQL table. Say for example this is my XML fragment.
<foo><key>1</key><description>One</description></foo>
<foo><key>2</key><description>Two</description></foo>
My table looks like this:
key SomeInfo
1 SomeInfo1
2 SomeInfo2
I want to be able to create a Stored procedure that takes my XML fragment as an xml type and then create a SQL select statement that looks like this in pseudo code.
Select myTable.key, myTable.SomeInfo, @.myXML.Description
From myTable and @.myXML
Where myTable.key = @.myXML.key
The result would then look like this
key SomeInfo Description
1 SomeInfo1 One
2 SomeInfo2 Two
Anyone have any ideas. I've got it where my XML and my table are joined by doing an @.myXML.exist(/foo/key[.=sql:column("myTable.key")] = 1 thing but I can't get the right Description value to show up in my row. I always get the first row.
Just to be complete. Here's my present stab at it.
Declare @.myXML xml
Set @.myXML = '<foo><key>1</key><description>One</description></foo>
<foo><key>2</key><description>Two</description></foo>'
Select myTable.key, myTable.SomeInfo, @.myTable.value('/foo/Description[1]','varchar(15)')
From myTable
Where @.myXML.exist('/foo/key[.=sql:column("myTable.key")]') = 1
I think you are looking for the XQuery "nodes()" function. It sounds like you want to reach into the column that is storing the XML, extract scalar values from that xml and join it with a relational table. If this is what you are trying to do, then the nodes() function should work for you.
Here is an example using the nodes() function on the xml data type.
http://msdn2.microsoft.com/en-us/ms188282.aspx
If you go this route, then your query would be something like:
SELECT myTable.key, myTable.SomeInfo, T(c).value('/description[1]', 'varchar(max)') as Description
FROM myTable, @.myXml.nodes('/foo') AS T(c)
WHERE myTable.key = T(c).value('/key[1]', 'int')
|||
Thanks for the reply,
I tried what you suggested and I actually don't get any results back at all. I first had to change the T(c).value to be T.c.value to get it to work and then when it ran it gave me back an empty result set.
|||Sorry about that I pressed enter before putting in my final results.
Your suggestion worked very well. Here is the final working query:
Select myTable.key,myTable.SomeInfo, T.c.value('./description[1]','varchar(max)') As Description
from myTable,@.myXML.nodes('/foo') As T(c)
Where myTable.key = T.c.value('./key[1]','int')
