January 15, 2009 at 5:23 pm
I need to shred some xml out into different columns based on an attribute value(code) but can't figure out a way to do it...my basic xml is below. I need to take each node and if it's an architect make that it's own column, owner another, etc. Keeping all the values for each node associated correctly and returning a column if the node isn't there, for instance not all of the nodes will have both owner and architect but I still need to return that column. CAN ANYONE HELP?? I've been working on this for a while now.
had to use brackets because I couldn't get it to post
[reports]
[report]
[title]ASDF[/title]
[project-contact-information]
[project-contact]
[contact-role code= "00001"]Owner (Public)[/contact-role]
[addr][/addr]
[/project-contact]
[project-contact]
[contact-role code= "00002"]Architect[/contact-role]
[addr][/addr]
[/project-contact]
[/project-contact-information]
[/report]
[/reports]
March 4, 2009 at 8:52 pm
OK, here's one possible solution, but with a caveat. I'm not a fan of the
query('./../../{somenode}') syntax. Although it works, I've seen some awful performance with climbing back up a relative path. Other than that, just replace the curly braces below with the proper XML tag bondaries and off you go...
declare @wodge xml
select @wodge = '{reports}
{report}
{title}ASDF{/title}
{project-contact-information}
{project-contact}
{contact-role code= "00001"}Owner (Public){/contact-role}
{addr}{/addr}
{/project-contact}
{project-contact}
{contact-role code= "00002"}Architect{/contact-role}
{addr}{/addr}
{/project-contact}{/project-contact-information}{/report}
{report}
{title}Booyah{/title}
{project-contact-information}
{project-contact}
{contact-role code= "00001"}Owner (Public){/contact-role}
{addr}{/addr}
{/project-contact}
{/project-contact-information}{/report}
{report}
{title}XYZ{/title}
{project-contact-information}
{project-contact}
{contact-role code= "00002"}Architect{/contact-role}
{addr}{/addr}
{/project-contact}{/project-contact-information}{/report}
{/reports} '
declare @contacts table (contactCodevarchar(20)
,contactRolevarchar(20)
,titlevarchar(20)
,projectContactXMLnodexml
)
insert into @contacts-- because you don't want to have to shred the XML more often than you have to
select pc.value('(./contact-role/@code)[1]','varchar(20)') as contactRole
,pc.value('(./contact-role)[1]','varchar(20)') as contactRole
,pc.value('(./../../title)[1]','varchar(20)') as title
,pc.query('.') as projectContactXMLnode
from @wodge.nodes('/reports/report/project-contact-information/project-contact') as X1(pc)
select t1.title, c1.projectContactXMLnode as Owner, c2.projectContactXMLnode as Architect
from
(
select title.value('(.)[1]','varchar(20)') as title
from @wodge.nodes('/reports/report/title') as X1(title)
) as t1
left outer join @contacts c1
on c1.title = t1.title
and c1.contactCode = '00001'
left outer join @contacts c2
on c2.title = t1.title
and c2.contactCode = '00002'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply