Solved

XML field in SQL - remove some nodes

Posted on 2010-08-19
12
475 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 69

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

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 69

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 69

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 69

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 69

Author Closing Comment

by:Éric Moreau
ID: 33499796
Brilliant
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

920 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now