query to return values from XML field

Question

Post reply

query to return values from XML field

Jampandu

Ten Centuries

Points: 1062
More actions
June 5, 2014 at 11:46 am

#308772

Hi ,
Can you help me in query to return values from XML field
create table #temp
(
id int identity (1,1)
,FieldSet XML
)
INSERT INTO #temp
VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')
,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')
SELECT * FROM #temp
Expected Output :
Id Column1Column2Column3Column4
1value1value2nullnull
2nullnullvalue3value4

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

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

Matt Crowley SSCarpal Tunnel Points: 4163 More actions · Answer 1

select FieldSet.value ('(/Fields/Field[@Key="Column1"]/@value)[1]', 'varchar(20)'),

FieldSet.value ('(/Fields/Field[@Key="Column2"]/@value)[1]', 'varchar(20)'),

FieldSet.value ('(/Fields/Field[@Key="Column3"]/@value)[1]', 'varchar(20)'),

FieldSet.value ('(/Fields/Field[@Key="Column4"]/@value)[1]', 'varchar(20)')

from #temp

Jampandu Ten Centuries Points: 1062 More actions · Answer 2

Thanks Matt!

Is there any way to select columns names dynamically as we may add more key values pairs in XML in near future.

just like 4th row

INSERT INTO #temp

VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')

,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')

,('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field><Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')

,('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field><Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> <Field Key="Column5" value="value5" > </Field> <Field Key="Column6" value="value6"> </Field><Field Key="Column7" value="value7" > </Field> <Field Key="Column8" value="value8"> </Field> </Fields>')

I don't have control on column names.

Thanks!

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

You probably want to split this into two different exercises. First extract the column name and values, and then use a dynamic pivot table to rebuild your rows.

The Dynamic pivot technique is described here[/url].

In order to pull the data you want to pivot, you're looking at a somewhat different XML extraction:

create table #temp

(

id int identity (1,1)

,FieldSet XML

)

INSERT INTO #temp

VALUES ('<Fields> <Field Key="Column1" value="value1" > </Field> <Field Key="Column2" value="value2"> </Field> </Fields>')

,('<Fields> <Field Key="Column3" value="value3" > </Field> <Field Key="Column4" value="value4"> </Field> </Fields>')

,('<Fields> <Field Key="Column1" value="value1" ></Field>

<Field Key="Column2" value="value2"> </Field>

<Field Key="Column3" value="value3" > </Field>

<Field Key="Column4" value="value4"> </Field>

</Fields>')

,('<Fields> <Field Key="Column1" value="value1" > </Field>

<Field Key="Column2" value="value2"> </Field>

<Field Key="Column3" value="value3" > </Field>

<Field Key="Column4" value="value4"> </Field>

<Field Key="Column5" value="value5" > </Field>

<Field Key="Column6" value="value6"> </Field>

<Field Key="Column7" value="value7" > </Field>

<Field Key="Column8" value="value8"> </Field>

</Fields>')

--actual query starts here

select ID, F.value ('(@Key)[1]', 'varchar(20)') ColumnName, F.value ('(@value)[1]', 'varchar(20)') ColumnValue

into #temp2

from #temp cross apply fieldset.nodes('/Fields/Field') g(F)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jampandu Ten Centuries Points: 1062 More actions · Answer 4

Thanks Miller,

Query working as expected , here is the query I used for dynamic pivot from #temp2 result

DECLARE @sqlNVARCHAR(MAX)

DECLARE @dynamic_pivotNVARCHAR(MAX)

SET @dynamic_pivot = STUFF(( SELECT DISTINCT ',' + ColumnName FROM #temp2 FOR XML RAW, ELEMENTS, TYPE ).value('.', 'NVARCHAR(MAX)'), 1, 1, '' )

SET @sql = 'SELECT *

FROM #temp2 us

PIVOT ( MAX( ColumnValue ) FOR ColumnName In ( ' + @dynamic_pivot + ' ) ) AS pvt'

EXEC(@sql)