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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:38 PM
Points: 11, Visits: 52
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:38 PM
Points: 11, Visits: 52
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 @ 12:28 PM
Points: 7,075, Visits: 15,324
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
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 8:38 PM
Points: 11, Visits: 52
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