XML data to columns

Question

Post reply

XML data to columns

Steven.Grzybowski

Hall of Fame

Points: 3214
More actions
June 30, 2016 at 6:54 am

#400344

I am really struggling with some xml data in a column, and how to split it into multiple columns.
Right now, data is stored as an nvarchar(MAX) field, but I am able to cast it as xml.
a basic example of the query I am using is as follows
SELECT TOP 1000
[AppActionNameID]
,CAST([AppActionParams] AS XML)
FROM [Mydb].[myschema].[mytable]
The XML is formatted as below
<BounceProperties>
<Name>Bounce Category</Name>
<Value>Hard bounce - User Unknown</Value>
<Name>Bounce Type</Name>
<Value>immediate</Value>
<Name>SMTP Code</Name>
<Value>550</Value>
<Name>SMTP Reason</Name>
<Value>smtp;550 email@testsite.net...User unknown</Value>
</BounceProperties>
I am trying to get each of those sections in the name tags to be their own column when I query it I have tried reading the MSDN documentation on how to query XML, and have looked at a bunch of examples on stack overflow, but have no real idea how XML data works with sql, and was hoping someone could point me in the correct direction. The whole xquery thing is just very confusing to me.

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

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

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 1

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

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 2

Slight difference in the query but huge in performance by using the text() function

😎

;WITH XML_CONVERT AS

(

SELECT

XN.XN_ID

,CONVERT(XML,XN.XN_VALUE,1) AS XML_VAL

FROM dbo.TBL_XML_NVARCHAR XN

)

SELECT TOP 1000

XC.XN_ID

,XC.XML_VAL.value('(/BounceProperties/Name/text())[1]','varchar(20)') AS Name1

,XC.XML_VAL.value('(/BounceProperties/Value/text())[1]','varchar(50)') AS Value1

,XC.XML_VAL.value('(/BounceProperties/Name/text())[2]','varchar(20)') AS Name2

,XC.XML_VAL.value('(/BounceProperties/Value/text())[2]','varchar(50)') AS Value2

,XC.XML_VAL.value('(/BounceProperties/Name/text())[3]','varchar(20)') AS Name3

,XC.XML_VAL.value('(/BounceProperties/Value/text())[3]','varchar(50)') AS Value3

,XC.XML_VAL.value('(/BounceProperties/Name/text())[4]','varchar(20)') AS Name4

,XC.XML_VAL.value('(/BounceProperties/Value/text())[4]','varchar(50)') AS Value4

FROM XML_CONVERT XC;

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 3

Eirikur Eiriksson (7/17/2016)
Slight difference in the query but huge in performance by using the text() function
😎
;WITH XML_CONVERT AS
(
SELECT
XN.XN_ID
,CONVERT(XML,XN.XN_VALUE,1) AS XML_VAL
FROM dbo.TBL_XML_NVARCHAR XN
)
SELECT TOP 1000
XC.XN_ID
,XC.XML_VAL.value('(/BounceProperties/Name/text())[1]','varchar(20)') AS Name1
,XC.XML_VAL.value('(/BounceProperties/Value/text())[1]','varchar(50)') AS Value1
,XC.XML_VAL.value('(/BounceProperties/Name/text())[2]','varchar(20)') AS Name2
,XC.XML_VAL.value('(/BounceProperties/Value/text())[2]','varchar(50)') AS Value2
,XC.XML_VAL.value('(/BounceProperties/Name/text())[3]','varchar(20)') AS Name3
,XC.XML_VAL.value('(/BounceProperties/Value/text())[3]','varchar(50)') AS Value3
,XC.XML_VAL.value('(/BounceProperties/Name/text())[4]','varchar(20)') AS Name4
,XC.XML_VAL.value('(/BounceProperties/Value/text())[4]','varchar(50)') AS Value4
FROM XML_CONVERT XC;

Interesting!

____________________________________________________

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

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 4

Mark Cowne (7/17/2016)
Interesting!

Yes indeed and often overlooked.

The difference is quite large, without the text() function, each value method results in three XML function calls, sort, joins etc., 15 operators in total. With the text() function it does a single XML function call per branch and the total of 5 operators.

😎

Full test set

USE TEEST;

GO

SET NOCOUNT ON;

--http://www.sqlservercentral.com/Forums/FindPost1802350.aspx

IF OBJECT_ID(N'dbo.TBL_XML_NVARCHAR') IS NOT NULL DROP TABLE dbo.TBL_XML_NVARCHAR;

CREATE TABLE dbo.TBL_XML_NVARCHAR

(

XN_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_NVARCHAR_XN_ID PRIMARY KEY CLUSTERED

,XN_VALUE NVARCHAR(MAX) NOT NULL

);

DECLARE @NVCXML NVARCHAR(MAX) = N'

<BounceProperties>

<Name>Bounce Category</Name>

<Value>Hard bounce - User Unknown</Value>

<Name>Bounce Type</Name>

<Value>immediate</Value>

<Name>SMTP Code</Name>

<Value>550</Value>

<Name>SMTP Reason</Name>

<Value>smtp;550 email@testsite.net...User unknown</Value>

</BounceProperties>';

;WITH T(N) AS (SELECT X.N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0))AS X(N))

, NUMS(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS N FROM T T1,T T2,T T3)

INSERT INTO dbo.TBL_XML_NVARCHAR(XN_VALUE)

SELECT

@NVCXML

FROM NUMS NM;

-- text() function