Solved

XML field in SQL - remove some nodes

Posted on 2010-08-19
12
477 Views
Last Modified: 2012-05-10
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?
0
Comment
Question by:Éric Moreau
  • 6
  • 6
12 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474398
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
 
LVL 70

Author Comment

by:Éric Moreau
ID: 33474420
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474458
I don't understand - you want to group by the XML field (raw text)? After convert(varchar(max) ?
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 70

Author Comment

by:Éric Moreau
ID: 33474505
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474711
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33474735

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
 
LVL 70

Author Comment

by:Éric Moreau
ID: 33474814
and what if I have other values I want to exclude? FieldID for example?
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 33474840
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
 
LVL 70

Author Comment

by:Éric Moreau
ID: 33474967
But SourceID is not a key, it is exactly like FieldID
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33479992
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
 
LVL 70

Author Comment

by:Éric Moreau
ID: 33480146
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
 
LVL 70

Author Closing Comment

by:Éric Moreau
ID: 33499796
Brilliant
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question