Solved

Need help with sql Xquery

Posted on 2010-09-09
14
709 Views
Last Modified: 2013-11-11
Hello Experts - I need your help ASAP. URGENT. Please help.

I have a xml in the following format.

<root>
<element id='abc'>text1<element>
<element id='ddd'>text2<element>
<element id='eee'>text3<element>
<element id='ggg'>text4<element>
</root>


I want to modify each of this element text as follows

a) replace the element text

b) replace the element attribute value with the element position.


I am trying to use XQUERY. i am unable to loop through each element in the xml and apply .modify(replace..) on it.

If there are other ways of doing this update without using xquery, i am open to that too.

The final result should look like

<root>
<element id='1'>hello text1<element>
<element id='2'>hello text2<element>
<element id='3'>hello text3<element>
<element id='4'>hello text4<element>
</root>
0
Comment
Question by:leo2007
[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
  • 5
  • 5
  • 4
14 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33642488
sql server xquery does not xsupport fn:position() so have to use a temp table to generate the positional IDs
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), xml xml)
insert @tmp select n.col.query('.') from @xml.nodes('root/element') n(col)

set @xml = convert(xml,(
	select t.id 'id', 'hello ' + n.col.value('.','varchar(10)') element
	from @tmp t
	cross apply t.xml.nodes('element') n(col)
	order by t.id
	for xml path('root')
	)).query(
'<root>{
   for $e in root
   return 
     <element id=''{$e/id/text()}''>hello{$e/id/text()} {data($e/element[1]/text())}</element>
 }</root>')

select @xml

Open in new window

0
 

Author Comment

by:leo2007
ID: 33646774
Thank you. I tried a little tweak to your logic

I am trying to return  element  position as a  separate attribute and also  return the id attribute value as it is.

The attribute id value is coming out blank.  I was expecting the attribute value  'abc','ddd','eee' etc in there.

What am i doing wrong here?
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), xml xml)
insert @tmp select n.col.query('.') from @xml.nodes('root/element') n(col)

set @xml = convert(xml,(
	select t.id 'pos'
, 'hello ' + n.col.value('.','varchar(10)') element
	from @tmp t
	cross apply t.xml.nodes('element') n(col)
	order by t.id
	for xml path('root')
	)).query(
'<root>{
   for $e in root
   return 
     <element pos
=''{$e/pos
/text()}'' id=''{$e/id/text()}''

 >hello{$e/id/text()} {data($e/element[1]/text())}</element>
 }</root>')


select @xml

Open in new window

0
 

Author Comment

by:leo2007
ID: 33648213
cyberkiwi - any suggestions.help for my followup question?
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33653173
Hi,
Please try this.
Uncomment the middle block to see what the intermediate xml looks like.
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), xml xml)
insert @tmp select n.col.query('.') from @xml.nodes('root/element') n(col)

/*   select t.id 'pos', n.col.value('@id','varchar(10)') id, n.col.value('.','varchar(10)') element
     from @tmp t
     cross apply t.xml.nodes('element') n(col)
     order by t.id
     for xml path('root') */

set @xml = convert(xml,(
     select t.id 'pos', n.col.value('@id','varchar(10)') id, n.col.value('.','varchar(10)') element
     from @tmp t
     cross apply t.xml.nodes('element') n(col)
     order by t.id
     for xml path('root')
     )).query(
'<root>{
  for $e in root
  return
     <element pos=''{$e/pos/text()}'' id=''{$e/id/text()}'' >hello{$e/pos/text()} {data($e/element[1]/text())}</element>
}</root>')

select @xml

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33659030
Hmmmm... Always found it easier to get back to basics... And a little bit confused with how you want it to look given the original example and some of the comments...


declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), element_id varchar(20), element_text varchar(20))
insert @tmp select n.col.value('@id','varchar(20)'),n.col.value('.','varchar(20)') from @xml.nodes('root/element') n(col)

select id '@id', element_id + ' ' + element_text from @tmp for xml path('element') ,root('root'), type, elements

-- and of course you could do set @xml = (the same select as above)
-- or

select id '@id', 'Hello ' + element_id + ' ' + element_text from @tmp for xml path('element') ,root('root'), type, elements

-- but now, all it is is a simple select statement to reformat the xml as you want...
0
 

Author Comment

by:leo2007
ID: 33663573
Thank you both. I want to retain the id attribute in the output. The output should look like this

<root>
  <element pos="1"  id=''abc=''>text1</element>
  <element pos="2"  id=''ddd=''>text2</element>
  <element pos="3"  id=''eee=''>text3</element>
  <element pos="4"  id=''ggg=''>text4</element>
</root>
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 250 total points
ID: 33664808
Gives result in http:#a33663573 verbatim
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), xml xml)
insert @tmp select n.col.query('.') from @xml.nodes('root/element') n(col)

set @xml = convert(xml,(
     select t.id 'pos', n.col.value('@id','varchar(10)') id, n.col.value('.','varchar(10)') element
     from @tmp t
     cross apply t.xml.nodes('element') n(col)
     order by t.id
     for xml path('root')
     )).query(
'<root>{
  for $e in root
  return
     <element pos=''{$e/pos/text()}'' id=''{$e/id/text()}'' >{data($e/element[1]/text())}</element>
}</root>')

select @xml

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33664820
Interesting format you are building there... Have to resort to "explicit" format

declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (pos int identity(1,1), element_id varchar(20), element_text varchar(20))
insert @tmp select n.col.value('@id','varchar(20)'),n.col.value('.','varchar(20)') from @xml.nodes('root/element') n(col)
 
SELECT 1 as Tag,NULL as Parent, NULL as [root!1!Data!element],NULL as [element!2!pos],NULL as [element!2!id],NULL as [element!2!]
UNION ALL
Select 2,1,NULL, pos, element_id, element_text from @tmp order by 4 FOR XML Explicit
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33664852
Or easier using Mark's way

@Mark,

Could you please explain why element_text alone (without + '') doesn't work?
Can't say I have seen the combination of path, root, type AND elements together before - but it is real neat!
declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (id int identity(1,1), element_id varchar(20), element_text varchar(20))
insert @tmp select n.col.value('@id','varchar(20)'),n.col.value('.','varchar(20)') from @xml.nodes('root/element') n(col)

select id '@pos', element_id '@id', element_text +'' from @tmp for xml path('element') ,root('root'), type, elements

Open in new window

0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33664870
@Mark 33664820

Okay just saw your post, but my guess would be it is because it doesn't have a name (that's why I tried it without giving a name) so it becomes just text() rather than a child node.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 33664928
Yep, it is simply making sure that it will exist without a name - "explicit" is really good that way...

Or, can do as you did and construct so that  there is no name,

Or, explicitly tell it there is no name :

declare @xml xml
set @xml = '<root>
<element id=''abc''>text1</element>
<element id=''ddd''>text2</element>
<element id=''eee''>text3</element>
<element id=''ggg''>text4</element>
</root>'

declare @tmp table (pos int identity(1,1), element_id varchar(20), element_text varchar(20))
insert @tmp select n.col.value('@id','varchar(20)'),n.col.value('.','varchar(20)') from @xml.nodes('root/element') n(col)

select pos '@pos', element_id '@id', element_text 'text()' from @tmp for xml path('element') ,root('root'), type, elements
 

 (Also changed the names in the table :D )
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33665015
Just noticed a spurious "=" at the end of the original ID - is that really required ??? If so, need to contatenate element_id + '='  but really doesnt look right.
0
 

Author Closing Comment

by:leo2007
ID: 33674774
Thank you both. You guys are genius.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 33674978
Very happy to have been of some help :)

and always happy to work with cyberkiwi :)
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

627 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