Solved

Need help with sql Xquery

Posted on 2010-09-09
14
707 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

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