Solved

XML field in SQL - remove some nodes

Posted on 2010-08-19
12
474 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 69

Author Comment

by:Éric Moreau
Comment Utility
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
Comment Utility
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
Comment Utility
But SourceID is not a key, it is exactly like FieldID
0
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
Comment Utility
Brilliant
0

Featured Post

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.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
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.

743 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

13 Experts available now in Live!

Get 1:1 Help Now