RE: XML data to columns – SQLServerCentral

One Orange Chip

Points: 26944

June 30, 2016 at 8:10 am

If you have a fixed number of Name/Value pairs you can do this

SELECT TOP 1000

[AppActionNameID]

,CAST([AppActionParams] AS XML)

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[1])[1]','varchar(20)') AS Name1

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Value[1])[1]','varchar(50)') AS Value1

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[2])[1]','varchar(20)') AS Name2

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Value[2])[1]','varchar(50)') AS Value2

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[3])[1]','varchar(20)') AS Name3

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Value[3])[1]','varchar(50)') AS Value3

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Name[4])[1]','varchar(20)') AS Name4

,CAST([AppActionParams] AS XML).value('(/BounceProperties/Value[4])[1]','varchar(50)') AS Value4

FROM [myschema].[mytable]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum