[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

XML field in SQL - remove some nodes

Posted on 2010-08-19
12
Medium Priority
?
485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
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 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

650 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