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?
 
cyberkiwiConnect With a Mentor Commented:
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
 
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
cyberkiwiCommented:
I don't understand - you want to group by the XML field (raw text)? After convert(varchar(max) ?
0
 
É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
 
É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
All Courses

From novice to tech pro — start learning today.