Solved

XML field in SQL - remove some nodes

Posted on 2010-08-19
12
478 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 59
Need to merge 3 large tables into one Table in SQL server 2 19
SCOM to SQL port 14xx failed? 1 29
Access #Deleted data 20 34
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 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