Is it possible to crosstab xml column

Question

Is it possible to crosstab xml column

Stringzz

Ten Centuries

Points: 1266
More actions
May 3, 2010 at 3:37 pm

#146772

I have a table with a column that has an xml datatype. Is there a way for me to enumerate through the field names and cross tab the names as column headers and the value as the data? the field names could change and won't always be the same names, so I can't hardcode the field names in the query.
Example of data in column:
<fields>
<field name="AMOUNT" value="20001.76" />
<field name="SALES_ID" value="654147" />
<field name="DATE" value="20100201" />
<field name="CUST_ID" value="4" />
</fields>
Query Result would look like:
CREDIT_LIMIT SALES_ID DATE PROVIDER_ID
20001.76 654147 20100201 4
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

LutzM SSC Guru Points: 107049 More actions · Answer 1

I assume you're familiar with shredding those data into a relational (temp) table using XQuery. If not, please post back and we'll show you an example how to do it.

Once you have the table populated, you could use the DynamicCrossTab concept as described in the related link in my signature.

I don't know of any XQuery method to apply directly to the XML data but I would expect such a concept as performing second best... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 2

I have a question: If you cannot know what the field names will be and they can change, are you only selecting one row at a time?

Otherwise you are going to have one hell of a time with different column names coming from each row?

Of course, if the field names can only come from a distinct set of possible values, then you don't need to go dynamic or single row...

An example of converting the xml to pivoted data when field names come from a restricted known set...

--= play table

declare @t table(id int identity(1,1) not null primary key,xmlcol xml)

--= some test data

insert @t (xmlcol) values('<fields>

<field name="AMOUNT" value="20001.76" />

<field name="SALES_ID" value="654147" />

<field name="DATE" value="20100201" />

<field name="CUST_ID" value="4" />

</fields>')

--= this one has SUPP_ID instead of CUST_ID

insert @t (xmlcol) values('<fields>

<field name="AMOUNT" value="20001.76" />

<field name="SALES_ID" value="654147" />

<field name="DATE" value="20100201" />

<field name="SUPP_ID" value="44" />

</fields>')

--= relies on there being a fixed number of possible "field names"

select id,AMOUNT,SALES_ID,DATE,CUST_ID,SUPP_ID

from (

select

t.id

,f.name

,f.value

from @t t

--= blow out the rows from the xml column

cross apply xmlcol.nodes('//field') as xmlrows(data)

--= convert the nodes to two columns, field: name and value

cross apply (

select

xmlrows.data.value('./@name[1]','varchar(120)') as field_name

,xmlrows.data.value('./@value[1]','varchar(120)') as field_value

) as f(name,value)

) inner_table

pivot (

max(inner_table.value)

for

inner_table.name

in

( AMOUNT

,SALES_ID

,DATE

,CUST_ID

,SUPP_ID

)

) pvtfname

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Stringzz Ten Centuries Points: 1266 More actions · Answer 3

The query would have criteria on another column in the table, so the field names from the xml column would all be the same within the query. The table would have other fields too, but the query would choose the same fields. There might be 25 different types within the table, so I don't want to hard-code for each query, I would want the query to dynamically grab the field names.

Stringzz Ten Centuries Points: 1266 More actions · Answer 4

@mister.magoo,

I tried replacing your column names "for inner_table.name in" with sub select sql below, which runs correctly on its own, but doesn't work within the whole query. Any ideas? This would provide the solution I need, I would just have to replace the task in the where clause. Also, the same Where clause is up in the select statement. Your query works good when I specify the column names for the inner table, but it does not run with the sub select.

for inner_table.name in (

select distinct a.name

from table b

--= blow out the rows from the xml column

cross apply xml_column.nodes('//field') as xmlrows(data)

--= convert the nodes to one column, field: name

cross apply (select xmlrows.data.value('./@name[1]','varchar(120)') as field_name

) as a(name)

where b.task = 'Task1'

)

Error Message

Msg 156, Level 15, State 1, Line 20

Incorrect syntax near the keyword 'select'.

Msg 102, Level 15, State 1, Line 28

Incorrect syntax near ')'.

Thanks

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

If the field names are truly dynamic then you need to follow Lutz's advice and go down the dynamic route.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Stringzz Ten Centuries Points: 1266 More actions · Answer 6

I was able to create a dynamic query with the following code, it works great. I am using different column names different amount of columns.

The key to make this run was to add the FOR XML PATH('') at the end of the select to get column names

Thanks for your help Mister Magoo and Lutz!

--= declare variables

declare @ListCol VARCHAR (1000)

, @Query VARCHAR (2000)

, @TaskName VARCHAR (100)

set @ListCol = ''

set @Query = ''

set @TaskName = 'Task 2'

--= some test data

create table testxml (id int identity(1,1) not null primary key

,task_name varchar(100)

,xmlcol xml)

insert testxml (task_name, xmlcol) values('Task 1','<fields>

<field name="AMOUNT" value="20001.76" />

<field name="SALES_ID" value="654147" />

<field name="DATE" value="20100201" />

<field name="CUST_ID" value="4" />

</fields>')

--= this one has different column names and more columns

insert testxml (task_name, xmlcol) values('Task 2','<fields>

<field name="SALES" value="20001.76" />

<field name="EMP_ID" value="654147" />

<field name="DATE_CREATED" value="04/02/2010" />

<field name="SUPP_ID" value="44" />

<field name="CUST_FIRST_NAME" value="John" />

<field name="CUST_LAST_NAME" value="Doe" />

</fields>')

select * from testxml

--= create column names list for the given task name

select @ListCol = (select distinct a.name + ','

FROM testxml b

cross apply xmlcol.nodes('//field') as xmlrows(data)

cross apply (select xmlrows.data.value('./@name[1]','varchar(120)') as field_name

) AS a(name)

where b.task_name = @TaskName

for xml path(''))

set @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)

--= dynamic query defined

set @Query = '

select *

from (

select

t.id

,t.task_name

,f.name

,f.value

from testxml t

--= blow out the rows from the xml column

cross apply xmlcol.nodes(''//field'') as xmlrows(data)

--= convert the nodes to two columns, field: name and value

cross apply (

select

xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name

,xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value

) as f(name,value)

where task_name = ''' + @TaskName + '''

) inner_table

pivot (

max(inner_table.value)

for

inner_table.name

in

(' + @ListCol + ')

) pvtfname

'

--= execute dynamic query

EXEC(@QUERY)

drop table testxml

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

Thanks for the feedback.

Just a small point, but worth noting....you should really use quotename() to protect your field names ... I have modded below.

Stringzz (5/4/2010)
I was able to create a dynamic query with the following code, it works great. I am using different column names different amount of columns.
The key to make this run was to add the FOR XML PATH('') at the end of the select to get column names
Thanks for your help Mister Magoo and Lutz!
--= declare variables
declare @ListCol VARCHAR (1000)
, @Query VARCHAR (2000)
, @TaskName VARCHAR (100)
set @ListCol = ''
set @Query = ''
set @TaskName = 'Task 2'
--= some test data
create table testxml (id int identity(1,1) not null primary key
,task_name varchar(100)
,xmlcol xml)
insert testxml (task_name, xmlcol) values('Task 1','<fields>
<field name="AMOUNT" value="20001.76" />
<field name="SALES_ID" value="654147" />
<field name="DATE" value="20100201" />
<field name="CUST_ID" value="4" />
</fields>')
--= this one has different column names and more columns
insert testxml (task_name, xmlcol) values('Task 2','<fields>
<field name="SALES" value="20001.76" />
<field name="EMP_ID" value="654147" />
<field name="DATE_CREATED" value="04/02/2010" />
<field name="SUPP_ID" value="44" />
<field name="CUST_FIRST_NAME" value="John" />
<field name="CUST_LAST_NAME" value="Doe" />
</fields>')
select * from testxml
--= create column names list for the given task name
select @ListCol = (select distinct QUOTENAME(a.name) + ','
FROM testxml b
cross apply xmlcol.nodes('//field') as xmlrows(data)
cross apply (select xmlrows.data.value('./@name[1]','varchar(120)') as field_name
) AS a(name)
where b.task_name = @TaskName
for xml path(''))
set @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)
--= dynamic query defined
set @Query = '
select *
from (
select
t.id
,t.task_name
,f.name
,f.value
from testxml t
--= blow out the rows from the xml column
cross apply xmlcol.nodes(''//field'') as xmlrows(data)
--= convert the nodes to two columns, field: name and value
cross apply (
select
xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name
,xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value
) as f(name,value)
where task_name = ''' + @TaskName + '''
) inner_table
pivot (
max(inner_table.value)
for
inner_table.name
in
(' + @ListCol + ')
) pvtfname
'
--= execute dynamic query
EXEC(@QUERY)
drop table testxml

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Adam Gojdas SSCommitted Points: 1766 More actions · Answer 8

Borrowing from the previous example, an additional way to go might be to use sp_executesql instead of EXEC. Then use some parameters where possible, i.e. "where task_name = @dynTaskName". (also I made the table #testxml temporary as to my preference) Like the following.:

--= declare variables

DECLARE @parmDefinition nvarchar(max);

declare @ListCol NVARCHAR (1000)

, @Query NVARCHAR (2000)

, @TaskName VARCHAR (100)

set @ListCol = N''

set @Query = N''

set @TaskName = 'Task 2'

--= some test data

create table #testxml (id int identity(1,1) not null primary key

,task_name varchar(100)

,xmlcol xml)

insert #testxml (task_name, xmlcol) values('Task 1','<fields>

<field name="AMOUNT" value="20001.76" />

<field name="SALES_ID" value="654147" />

<field name="DATE" value="20100201" />

<field name="CUST_ID" value="4" />

</fields>')

--= this one has different column names and more columns

insert #testxml (task_name, xmlcol) values('Task 2','<fields>

<field name="SALES" value="20001.76" />

<field name="EMP_ID" value="654147" />

<field name="DATE_CREATED" value="04/02/2010" />

<field name="SUPP_ID" value="44" />

<field name="CUST_FIRST_NAME" value="John" />

<field name="CUST_LAST_NAME" value="Doe" />

</fields>')

select * from #testxml

--= create column names list for the given task name

select @ListCol = (select distinct QUOTENAME(a.name) + ','

FROM #testxml b

cross apply xmlcol.nodes('//field') as xmlrows(data)

cross apply (select xmlrows.data.value('./@name[1]','varchar(120)') as field_name

) AS a(name)

where b.task_name = @TaskName

for xml path(''))

--SELECT @ListCol [@ListCol]

set @ListCol = SUBSTRING(@ListCol, 1, LEN(@ListCol)-1)

--SELECT @ListCol [@ListCol]

--= dynamic query defined

SET @parmDefinition = N'@dynTaskName varchar(100)';

set @Query = N'

select *

from (

select

t.id

,t.task_name

,f.name

,f.value

from #testxml t

--= blow out the rows from the xml column

cross apply xmlcol.nodes(''//field'') as xmlrows(data)

--= convert the nodes to two columns, field: name and value

cross apply (

select

xmlrows.data.value(''./@name[1]'',''varchar(120)'') as field_name

,xmlrows.data.value(''./@value[1]'',''varchar(120)'') as field_value

) as f(name,value)

where task_name = @dynTaskName

) inner_table

pivot (

max(inner_table.value)

for

inner_table.name

in

(' + @ListCol + N')

) pvtfname

'

--= execute dynamic query

EXEC dbo.sp_executesql

@Query

,@parmDefinition

,@dynTaskName=@TaskName

drop table #testxml

You should probably prefer to use sp_executesql over EXEC for dynamic sql because of its certain benefits when executing dynamic sql.