Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Need help with sql Xquery

Posted on 2010-09-09
14
Medium Priority
?
710 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
Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

 
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 1000 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 1000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

722 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