Need help with sql Xquery

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>
leo2007Asked:
Who is Participating?
 
cyberkiwiConnect With a Mentor Commented:
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
 
cyberkiwiCommented:
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
 
leo2007Author Commented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
leo2007Author Commented:
cyberkiwi - any suggestions.help for my followup question?
0
 
cyberkiwiCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
leo2007Author Commented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
cyberkiwiCommented:
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
 
cyberkiwiCommented:
@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
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
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
 
Mark WillsTopic AdvisorCommented:
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
 
leo2007Author Commented:
Thank you both. You guys are genius.
0
 
Mark WillsTopic AdvisorCommented:
Very happy to have been of some help :)

and always happy to work with cyberkiwi :)
0
All Courses

From novice to tech pro — start learning today.