XML field in SQL - remove some nodes

I have a XML field in a SQL 2005 database table containing something like this:

<options><option key="ROLE">2014</option><SourceID>289</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate><option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" /><option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>

I am trying to remove some nodes (like SourceID and ROLE) to group on the remaining nodes.

I can I do it?
LVL 71
Éric MoreauSenior .Net ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cyberkiwiCommented:
To get any xml data from the table, you need to use xpath like xmlcol.nodes or xmlcol.query
When doing that, you can already ignore whatever node you don't want.
Can you please elaborate?
0
Éric MoreauSenior .Net ConsultantAuthor Commented:
for example, instead of getting the original XML value:
<options><option key="ROLE">2014</option><SourceID>289</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate><option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" /><option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>
I want (SourceID and ROLE):
<options><FieldID>98</FieldID><EndDate>2010-08-19</EndDate><option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" /><option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>
If possible, I want it in a query because I have to group on the resulting field.
I know I can do it by opening a cursor and process the rows of my table one by one, but I would really prefer to do it set-based oriented.
0
cyberkiwiCommented:
I don't understand - you want to group by the XML field (raw text)? After convert(varchar(max) ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Éric MoreauSenior .Net ConsultantAuthor Commented:
Here is the pseudo-code of what I want:
select MyXmlField(minus ROLE and minus SourceID nodes), count(*)
from MyTable
Group By MyXmlField(minus ROLE and minus SourceID nodes)
0
cyberkiwiCommented:
Please modify to suit
with t(x) as
(
select convert(xml,'<options><option key="IN ORDER" />
<option key="ROLE">2014</option>
<SourceID>289</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate>
<option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" />
<option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>')
union all
select convert(xml,'<options>
<SourceID>489</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate>
<option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" />
<option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>')
)
select x, convert(xml,(
	select n.c.query('.')
	from x.nodes('options/node()[local-name()!="SourceID"]') n(c)
	where isnull(n.c.value('@key','varchar(10)'),'')!='ROLE'
	for xml path(''), root('options')))
from t

Open in new window

0
cyberkiwiCommented:

with t(x) as
(
select convert(xml,'<options><option key="IN ORDER" />
<option key="ROLE">2014</option>
<SourceID>289</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate>
<option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" />
<option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>')
union all
select convert(xml,'<options>
<SourceID>489</SourceID><FieldID>98</FieldID><EndDate>2010-08-19</EndDate>
<option key="WITHSOURCEINFO" /><option key="WITHFIELDINFO" />
<option key="WITHTARGETINFO" /><option key="WITHPROVIDERINFO" /></options>')
)
-------- ignore above, modify below t=tablename, x=xml col name
select convert(xml,clean), count(*)
from
(
select (
	select n.c.query('.')
	from x.nodes('options/node()[local-name()!="SourceID"]') n(c)
	where isnull(n.c.value('@key','varchar(10)'),'')!='ROLE'
	for xml path(''), root('options')) clean
from t
) SQ
group by clean

Open in new window

0
Éric MoreauSenior .Net ConsultantAuthor Commented:
and what if I have other values I want to exclude? FieldID for example?
0
cyberkiwiCommented:
for node names, keep adding to xpath

from x.nodes('options/node()[local-name()!="SourceID"][local-name()!="FieldID"]') n(c)

for attributes, use the null + value test

      where isnull(n.c.value('@key','varchar(10)'),'')!='ROLE'
        and isnull(n.c.value('@key','varchar(10)'),'')!='WITHPROVIDERINFO'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Éric MoreauSenior .Net ConsultantAuthor Commented:
But SourceID is not a key, it is exactly like FieldID
0
cyberkiwiCommented:
I don't understand your last question?
SourceID and FieldID are node names, so the filter is done in XPath, i.e. local-name()!=<nodename>
0
Éric MoreauSenior .Net ConsultantAuthor Commented:
I just re-read your previous comment and I saw that FieldID is concatenated on the from clause (read too fast, my fault). I will try it Monday morning and keep you posted.
0
Éric MoreauSenior .Net ConsultantAuthor Commented:
Brilliant
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.