Solved

Need help with sql Xquery

Posted on 2010-09-09
14
705 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
  • 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

803 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