December 30, 2009 at 7:10 am
I need to shred some xml (stored in a xml column in a table) into a table. I have attached a sample of the xml as well as the desired output to this post. The xml is data on website performance tests. Each test can have a step and the step has many components. I can pull out the test info from the xml and I can pull out the info on the components but I can't tie them together.
So the xml looks like
test
step
component
component
component
I need it in a table like
test component
test component
test component
I think this can be done using xquery with a little FLWOR magic.
December 30, 2009 at 7:38 am
Here's what I come up with for this one:
declare @XML XML;
select @XML = ' <test>
<node>Newark, NJ - Qwest</node>
<test_rt>2467</test_rt>
<timestamp>12/18/2009 4:46:27 PM</timestamp>
<step>
<seqno>0</seqno>
<step_rt>2467</step_rt>
<status>GOOD</status>
<component>
<compReturnCode>200</compReturnCode>
<compURL>http://www.XXXs.com/</compURL>
<compDNS>2</compDNS>
</component>
<component>
<compReturnCode>200</compReturnCode>
<compURL>http://www.773.com/</compURL>
<compDNS>0</compDNS>
</component>
</step>
</test>';
select
@XML.value('(/test/node/.)[1]','varchar(100)') as Node,
@XML.value('(/test/test_rt/.)[1]','varchar(100)') as test_rt,
@XML.value('(/test/timestamp/.)[1]','datetime') as [timestamp],
@XML.value('(/test/step/status/.)[1]','varchar(100)') as Node,
Comp.Node.query('.').value('(/component/compReturnCode/.)[1]','varchar(100)') as compReturnCode,
Comp.Node.query('.').value('(/component/compURL/.)[1]','varchar(100)') as compURL,
Comp.Node.query('.').value('(/component/compDNS/.)[1]','varchar(100)') as compDNS
from @XML.nodes('test/step/component') Comp(Node);
You'd have to use your column name instead of a variable, and might need to turn this into the subquery part of a Cross Apply in the From clause, but it should do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 8:41 am
Thanks for the quick reply. Your query gives me almost exactly what I want however when I apply it to a larger xml doc which contains several test elements the query will only return the values from the first element. I tried using cross apply to get the test values and apply it to the component values but I get a carterisian product. So I am missing how to relate the tests to their specific component elements.
I have attached the xml that I am working with. I was thinking about trying a looping approach as outline in this article
http://www.sqlservercentral.com/articles/XML/62290/
but I am sure there is a more elegant solution.
Thanks!
December 30, 2009 at 8:50 am
I did the same thing again, assigning the XML to a variable, then querying against the variable.
See if this gets you going in the right direction:
select
G.test.query('.'),
T.comp.query('.')
from @XML.nodes('gomez_data/monitor/test') G(test)
cross apply G.test.nodes('step/component') T(comp);
You'll have to build out the value queries, based on the ones in my prior solution.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2009 at 9:33 am
Yep that did it. Thanks very much for your assistance.
December 30, 2009 at 9:45 am
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply