Break XML into Columns

Hi,

I have a XML of the structure,

<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello</Column>
<Column name="Header2">hello</Column>
</XMLSET>

in my database (SQL - 2k5). Now I want to split it in a manner that yields me this output;

Header Header1 Header2

Hello Hello Hello

Any pointers on how to go about this ?

Thanks in Advance,

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name="Header"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Header1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Header2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||

Thanks for the reply....

A small query here..... Instead of hard coding the name of the column as Header, Header1 is there a way we can have the headers displayed dynamically... In short the following query will always return header, header1 and so. How to get it displayed dynamically?

select r.value('(./Column[@.name="Head"])[1]','varchar(10)') as Header,
r.value('(./Column[@.name="Headr1"])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name="Headr2"])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

Thanks again for the reply....

|||

If you want to have dynamic column names you'll have to use dynamic SQL.

Note that you can use variables for your search criteria as below.

declare @.h1 varchar(10)
declare @.h2 varchar(10)
declare @.h3 varchar(10)

set @.h1='Header'
set @.h2='Header1'
set @.h3='Header2'

declare @.x xml
set @.x='<XMLSET>
<Column name="Header">hello</Column>
<Column name="Header1">hello1</Column>
<Column name="Header2">hello2</Column>
</XMLSET>'

select r.value('(./Column[@.name=sql:variable("@.h1")])[1]','varchar(10)') as Header,
r.value('(./Column[@.name=sql:variable("@.h2")])[1]','varchar(10)') as Header1,
r.value('(./Column[@.name=sql:variable("@.h3")])[1]','varchar(10)') as Header2
from @.x.nodes('/XMLSET') as x(r)

|||Thanks Mark.. That was indeed what I was looking for |||

Hi

I am processing xml in sql server 2005. The values are stored in a column as xml datatype. How to query the data that is in the column. This is the structure of the xml.

'<skill>

<Coach type="AA">false</Coach>

<CDT type="AA">false</CDT>

<DIT type="AA">false</DIT>

<FSA type="AA">false</FSA>

<LMR type="AA">false</LMR>

<ROSPA type="Additional">false</ROSPA>

<IAM type="Additional">false</IAM>

<Diamond type="Additional">false</Diamond>

<DipDI type="Additional">false</DipDI>

<BTEC type="Additional">false</BTEC>

<QEF type="Additional">

<member>false</member>

<certificatenumber />

<level />

</QEF>

<BTec type="Additional">false</BTec>

</skill>'

Thanks

Srini

|||

Can you be bit specific here ?

You want the data in the XML as columns is it ?

|||

The data is in the xml field of a table. The xml contains the skills of a candidate. I need to access the skill set values like CDT=false, DIT = false, and additional qualifications, if any, have to be accessed.

Thanks,

Srini

|||

Not sure if I got ur requirement clear, but I infer you wanted to break open the XML nodes as columns. Create a table like this, insert the two values and then run the query.

create table test
(x xml)
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go
insert test values(
N'<XMLDATA>
<COLUMNS>
<Column name="Val1">sdf \r\nsdfsdfs sdfsdf</Column>
<Column name="Val2"> 2 </Column>
<Column name="Val3"> 3 </Column>
<Column name="Val4"> Test0 </Column>
<Column name="Val5"> Test1 </Column>
<Column name="Val6"> Test2 </Column>
<Column name="Val7"> Test3 </Column>
<Column name="Val8"> Test4 </Column>
</COLUMNS>
</XMLDATA>')
go

SELECT ref.value('@.name', 'varchar(50)') Header, ref.value('data(.)', 'varchar(50)') Val
FROM test cross apply x.nodes('/XMLDATA/COLUMNS/Column') as x(ref)

Hope that helps...