query to return values from XML field

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

query to return values from XML field

Author

Topic

Jampandu
Starting Member

12 Posts

Posted - 2014-06-05 : 13:50:11

Hi

Please 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>')
,('<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>')

SELECT * FROM #temp

Expected Output

Id Column1 Column2 Column3 Column4
1 value1 value2 null null
2 null null value3 value4
3 value1 value2 value3 value4

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-06-05 : 21:07:27

[code]SELECT
id,
FieldSet.value('(/Fields/Field[@Key="Column1"]/@value)[1]', 'varchar(20)') Column1,
FieldSet.value('(/Fields/Field[@Key="Column2"]/@value)[1]', 'varchar(20)') Column2,
FieldSet.value('(/Fields/Field[@Key="Column3"]/@value)[1]', 'varchar(20)') Column3,
FieldSet.value('(/Fields/Field[@Key="Column4"]/@value)[1]', 'varchar(20)') Column4
FROM #temp[/code]

-------------------------------------
From Japan
Sorry, my English ability is limited.

Jampandu
Starting Member

12 Posts

Posted - 2014-06-05 : 21:14:51

Thanks for your reply.

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>')

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources