Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

query to return values from XML field Expand / Collapse
Author
Message
Posted Thursday, June 5, 2014 11:46 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 31, 2015 9:24 PM
Points: 37, Visits: 125
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 Column1 Column2 Column3 Column4
1 value1 value2 null null
2 null null value3 value4
Post #1577967
Posted Thursday, June 5, 2014 12:15 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 10:20 AM
Points: 261, Visits: 469
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

Post #1577983
Posted Thursday, June 5, 2014 7:15 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 31, 2015 9:24 PM
Points: 37, Visits: 125
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!
Post #1578121
Posted Friday, June 6, 2014 4:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:42 PM
Points: 7,352, Visits: 16,694
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.

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?
Post #1578547
Posted Friday, June 6, 2014 6:39 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 31, 2015 9:24 PM
Points: 37, Visits: 125
Thanks Miller,

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


DECLARE @sql NVARCHAR(MAX)
DECLARE @dynamic_pivot NVARCHAR(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)

Post #1578552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse