xml to elements as columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

xml to elements as columns

Author

Topic

hrishy
Starting Member

47 Posts

Posted - 2008-10-16 : 11:18:41

Hi

In a table column i have the following xml


<role>A</role><role>B</role><role>C</role><role>D</role><role>E</role><role>F</role>

the output i need is

A
B
C
D
E
F

any way to do this using sql

regards
Hrishy

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-16 : 11:58:17

declare @xml xml

set @xml = (Select ColumnB from (Select top 1 ColumnB from Table_Name where ColumnA='test')a)

select @xml

select Attributes.[role].value(
'.'
,'varchar(max)'
) as AttributeValue
from @xml.nodes(
'//role'
) Attributes ([role])

hrishy
Starting Member

47 Posts

Posted - 2008-10-16 : 12:19:07

Hi Dardusky

Appreciate your solution very much.
Can this be done using sql and without resorting to t-sql.

no use of
set @xml =
and select @xml and all

regards
Hrishy

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-17 : 05:07:40

The "select @xml" is not needed - this was just to check the values in variable. You can remove it.
Do you want to run query without the variable @xml? Do you mind if I ask why?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-17 : 05:17:03

[code]DECLARE @Sample TABLE
(
rowID INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
data XML
)

INSERT @Sample
SELECT '<role>a</role><role>b</role><role>c</role><role>d</role><role>e</role><role>f</role>' UNION ALL
SELECT '<role>X</role><role>Y</role><role>Z</role><role>T</role>'

SELECT s.rowID,
g.i
FROM @Sample AS s
CROSS APPLY (
SELECT a.r.value('.' ,'VARCHAR(MAX)')
FROM data.nodes('//role') AS a(r)
) AS g(i)[/code]

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-17 : 05:48:10

Without using variable (why is beyond me):

select V.value(
'.'
,'varchar(max)'
) as AttributeValue
from
(
SELECT T2.Loc.query('.')as V
FROM TableName.[ColumnName] T
CROSS APPLY ColumnName.nodes('//role') as T2(Loc) )s

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-10-17 : 05:49:56

Sorry - with correction:

select V.value(
'.'
,'varchar(max)'
) as AttributeValue
from
(
SELECT T2.Loc.query('.')as V
FROM TableName T
CROSS APPLY ColumnName.nodes('//role') as T2(Loc) )s

hrishy
Starting Member

47 Posts

Posted - 2008-10-17 : 09:20:46

Hi DarKDusky Peso

Great thank you very much your soltion worked wunderfully.

regards
Hrishy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources