using XML method

Question

using XML method

Abhijit More

SSCertifiable

Points: 6897
More actions
January 10, 2011 at 6:01 am

#232837

DECLARE @xml AS XML =
'<Root>
<SubNode>
<Property1>Value 1</Property1>
<Property2>Value 2</Property2>
<Property3>Value 3</Property3>
</SubNode>
</Root>'
Different options to retrive Value 1, Value 2, ... in rows.
Abhijit - http://abhijitmore.wordpress.com

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Since you use different tags, I think that you can do it only with union all query. If you can modify the XML so the tag’s name will be the same all the time (for example Property instead of Property1, Property2…PropertyN), then it will be much easier. In any case here is a way of doing it:

set @xml =

'<Root>

<SubNode>

<Property1>Value 1</Property1>

<Property2>Value 2</Property2>

<Property3>Value 3</Property3>

</SubNode>

</Root>'

select tbl.col.value(('.'),'varchar(10)')

from @xml.nodes('Root/SubNode/Property1') tbl (col)

union all

select tbl.col.value(('.'),'varchar(10)')

from @xml.nodes('Root/SubNode/Property2') tbl (col)

union all

select tbl.col.value(('.'),'varchar(10)')

from @xml.nodes('Root/SubNode/Property3') tbl (col)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Adi Cohn-120898 (1/10/2011)
Since you use different tags, I think that you can do it only with union all query. If you can modify the XML so the tag’s name will be the same all the time (for example Property instead of Property1, Property2…PropertyN), then it will be much easier. In any case here is a way of doing it:
set @xml =
'<Root>
<SubNode>
<Property1>Value 1</Property1>
<Property2>Value 2</Property2>
<Property3>Value 3</Property3>
</SubNode>
</Root>'
select tbl.col.value(('.'),'varchar(10)')
from @xml.nodes('Root/SubNode/Property1') tbl (col)
union all
select tbl.col.value(('.'),'varchar(10)')
from @xml.nodes('Root/SubNode/Property2') tbl (col)
union all
select tbl.col.value(('.'),'varchar(10)')
from @xml.nodes('Root/SubNode/Property3') tbl (col)
Adi

Also this

select tbl.col.value(('.[1]'),'varchar(10)')

from @xml.nodes('/Root/SubNode/*') tbl (col)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 3

Also this
select tbl.col.value(('.[1]'),'varchar(10)')
from @xml.nodes('/Root/SubNode/*') tbl (col)

Very nice. Learned something new today:-)

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

durga.palepu Hall of Fame Points: 3530 More actions · Answer 4

durga.palepu

Hall of Fame

Points: 3530

July 9, 2016 at 6:09 am

#1889285

very useful, I too learned something new today.