How to read XML

Question

Post reply

How to read XML

BigB

SSC Eights!

Points: 828
More actions
July 21, 2016 at 2:10 am

#328026

Hi All,
I want to read/update values of Id1,Id2 in below XML which is stored in one of the table's XML column. Please let me know SQL for the same. I tried to search for this on google but didn't get solution.
<Audit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id1="1" Id2="64ebdbb8-dcb5-4fb6-a5e9-b3e10280e974" StatusId="1" IsFlag="false">
<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>
<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>
</Audit>

Viewing 12 posts - 1 through 11 (of 11 total)

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

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 1

Quick example, should be enough to get you passed this hurdle

😎

USE TEEST;

GO

SET NOCOUNT ON;

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

CREATE TABLE dbo.TBL_XML_MODIFICATION

(

XM_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_MODIFICATION_XM_ID PRIMARY KEY CLUSTERED

,XM_XML XML NOT NULL

);

DECLARE @TXML XML = N'<Audit

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

Id1="1"

Id2="64ebdbb8-dcb5-4fb6-a5e9-b3e10280e974"

StatusId="1"

IsFlag="false">

<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>

<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>

</Audit>';

INSERT INTO dbo.TBL_XML_MODIFICATION(XM_XML)

VALUES (@TXML);

DECLARE @NEW_ID1 INT = 1001;

DECLARE @NEW_ID2 VARCHAR(38) = '0000000-0000-0000-0000-000000000000';

SELECT

XM.XM_ID

,XM.XM_XML

FROM dbo.TBL_XML_MODIFICATION XM;

UPDATE dbo.TBL_XML_MODIFICATION

SET XM_XML.modify('

replace value of (Audit/@Id1)[1]

with ( sql:variable("@NEW_ID1") )')

WHERE XM_ID = 1;

UPDATE dbo.TBL_XML_MODIFICATION

SET XM_XML.modify('

replace value of (Audit/@Id2)[1]

with ( sql:variable("@NEW_ID2") )')

WHERE XM_ID = 1;

SELECT

XM.XM_ID

,XM.XM_XML

FROM dbo.TBL_XML_MODIFICATION XM;

XML Before

<Audit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id1="1" Id2="64ebdbb8-dcb5-4fb6-a5e9-b3e10280e974" StatusId="1" IsFlag="false">

<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>

<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>

</Audit>

XML After

<Audit xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Id1="1001" Id2="0000000-0000-0000-0000-000000000000" StatusId="1" IsFlag="false">

<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>

<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>

</Audit>

BigB SSC Eights! Points: 828 More actions · Answer 2

BigB

SSC Eights!

Points: 828

July 21, 2016 at 3:51 am

#1891252

Hi,

Thanks. That was really helpful.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 3

Updating from a column value is equally simple, here is a quick sample, results are equal to the previous method.

😎

USE TEEST;

GO

SET NOCOUNT ON;

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

CREATE TABLE dbo.TBL_XML_MODIFICATION

(

XM_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_MODIFICATION_XM_ID PRIMARY KEY CLUSTERED

,XM_XML XML NOT NULL

);

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

CREATE TABLE dbo.TBL_XML_NEW_VALUES

(

XNV_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_NEW_VALUES_XNV_ID PRIMARY KEY CLUSTERED

,XNV_ID1 INT NOT NULL

,XNV_GUID VARCHAR(38) NOT NULL

);

DECLARE @TXML XML = N'<Audit

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

Id1="1"

Id2="64ebdbb8-dcb5-4fb6-a5e9-b3e10280e974"

StatusId="1"

IsFlag="false">

<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>

<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>

</Audit>';

INSERT INTO dbo.TBL_XML_MODIFICATION(XM_XML)

VALUES (@TXML);

INSERT INTO dbo.TBL_XML_NEW_VALUES(XNV_ID1,XNV_GUID)

VALUES (1002,'0000000-0000-0000-0000-000000000000');

SELECT

XM.XM_ID

,XM.XM_XML

FROM dbo.TBL_XML_MODIFICATION XM;

UPDATE dbo.TBL_XML_MODIFICATION

SET XM_XML.modify('

replace value of (Audit/@Id1)[1]

with ( sql:column("XNV.XNV_ID1") )')

FROM dbo.TBL_XML_NEW_VALUES XNV

WHERE XM_ID = 1

AND XNV.XNV_ID = 1;

UPDATE dbo.TBL_XML_MODIFICATION

SET XM_XML.modify('

replace value of (Audit/@Id2)[1]

with ( sql:column("XNV.XNV_GUID") )')

FROM dbo.TBL_XML_NEW_VALUES XNV

WHERE XM_ID = 1

AND XNV.XNV_ID = 1;

SELECT

XM.XM_ID

,XM.XM_XML

FROM dbo.TBL_XML_MODIFICATION XM;

BigB SSC Eights! Points: 828 More actions · Answer 4

Hi

Thanks Again! Is that possible to make those two separate updates into single update statement to update ID1 and ID2?

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 5

Only one modification can be made at a time with the XML modify method, the workaround is to reconstruct the XML and replace the previous value, here is a quick example.

😎

USE TEEST;

GO

SET NOCOUNT ON;

--

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

CREATE TABLE dbo.TBL_XML_MODIFICATION

(

XM_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_MODIFICATION_XM_ID PRIMARY KEY CLUSTERED

,XM_XML XML NOT NULL

);

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

CREATE TABLE dbo.TBL_XML_NEW_VALUES

(

XNV_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_NEW_VALUES_XNV_ID PRIMARY KEY CLUSTERED

,XNV_ID1 INT NOT NULL

,XNV_GUID VARCHAR(38) NOT NULL

);

DECLARE @TXML XML = N'<Audit

xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="http://www.w3.org/2001/XMLSchema"

Id1="1"

Id2="64ebdbb8-dcb5-4fb6-a5e9-b3e10280e974"

StatusId="1"

IsFlag="false">

<StartDate>2016-04-07T14:35:40.090143+05:30</StartDate>

<EndDate>2016-04-07T14:35:42.6526678+05:30</EndDate>

</Audit>';

INSERT INTO dbo.TBL_XML_MODIFICATION(XM_XML)

VALUES (@TXML);

INSERT INTO dbo.TBL_XML_NEW_VALUES(XNV_ID1,XNV_GUID)

VALUES (1002,'0000000-0000-0000-0000-000000000000');

;WITH BASE_DATA AS

(

SELECT

XXM.XM_ID

,(

SELECT

XNV.XNV_ID1 AS '@Id1'

,XNV.XNV_GUID AS '@Id2'

,AUDT.DATA.value('@StatusId','INT') AS '@StatusId'

,AUDT.DATA.value('@IsFlag','VARCHAR(12)') AS '@IsFlag'

,AUDT.DATA.query('*')

FROM dbo.TBL_XML_MODIFICATION XM

INNER JOIN dbo.TBL_XML_NEW_VALUES XNV

ON XM.XM_ID = XNV.XNV_ID

CROSS APPLY XM.XM_XML.nodes('Audit') AS AUDT(DATA)

WHERE XXM.XM_ID = XM.XM_ID

FOR XML PATH('Audit')

) AS XML_VAL

FROM dbo.TBL_XML_MODIFICATION XXM

)

UPDATE XU

SET XU.XM_XML = BD.XML_VAL

FROM BASE_DATA BD

INNER JOIN dbo.TBL_XML_MODIFICATION XU

ON BD.XM_ID = XU.XM_ID

;

SELECT

XM.XM_ID

,XM.XM_XML

FROM dbo.TBL_XML_MODIFICATION XM;

nsadams87xx Old Hand Points: 386 More actions · Answer 6

I really struggled with XML and coding it for a while but one very high level and broad but helpful concept I formulated was to think about XML as a pivot. The levels of an XML record go right and left as opposed to up and down and each XML "item" go up and down as opposed to left and right.

BigB SSC Eights! Points: 828 More actions · Answer 7

Hi

Actually, I have one xml column and it contains data as below. I want to update value of all VCReqId in one update to another int column in same table.

<Purpose>To define business requirements.</Purpose>

<Type>Skills</Type>

<Value>Ability.</Value>

</JPDraftItem>

<Type>Skills</Type>

<Value>Difficulty</Value>

</JPDraftItem>

<Type>Skills</Type>

<Value>Practical</Value>

</JPDraftItem>

</JPDraftItems>

</JPDraft>

I tried but no luck.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

bhushanbagul (7/25/2016)
I tried but no luck.

Then try using an RDBMS the way it was meant to be instead of storing denormalized, tag bloated, mostly non-SARGable data in your database that will always slow your code down. Shred the XML upon receipt and put it into correctly normalized tables. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 9

As I mentioned before, reconstructing the XML with the changed values and do a normal update is one way of doing multiple updates on an XML in a single operation

😎

USE TEEST;

GO

SET NOCOUNT ON;

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

CREATE TABLE dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN

(

XUV_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_XML_UPDATE_VALUE_FROM_COLUMN_XUV_ID PRIMARY KEY CLUSTERED

,XUV_VALUE INT NOT NULL

,XUV_XML XML NOT NULL

);

DECLARE @TXML XML = N'<JPDraft xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

<JPDraftId>0</JPDraftId>

<Purpose>To define business requirements.</Purpose>

<DateCreated>2016-04-25</DateCreated>

<JPDraftItems>

<JPDraftItem>

<ItemId>1</ItemId>

<Type>Skills</Type>

<Value>Ability.</Value>

<VCReqId>1</VCReqId>

<ActId>67</ActId>

</JPDraftItem>

<JPDraftItem>

<ItemId>2</ItemId>

<Type>Skills</Type>

<Value>Difficulty</Value>

<VCReqId>1</VCReqId>

<ActId>73</ActId>

</JPDraftItem>

<JPDraftItem>

<ItemId>3</ItemId>

<Type>Skills</Type>

<Value>Practical</Value>

<VCReqId>1</VCReqId>

<ActId>63</ActId>

</JPDraftItem>

</JPDraftItems>

</JPDraft>';

INSERT INTO dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN(XUV_VALUE,XUV_XML)

SELECT 1007,@TXML;

;WITH BASE_DATA AS

(

SELECT

XU.XUV_ID

,(

SELECT

XU.XUV_XML.value('(JPDraft/JPDraftId/text())[1]','INT') AS 'JPDraftId'

,XU.XUV_XML.value('(JPDraft/Purpose/text())[1]','VARCHAR(50)') AS 'Purpose'

,XU.XUV_XML.value('(JPDraft/DateCreated/text())[1]','DATE') AS 'DateCreated'

,(SELECT

JPDRAFTITEM.DATA.query('ItemId/text()') AS ItemId

,JPDRAFTITEM.DATA.query('Type/text()') AS Type

,JPDRAFTITEM.DATA.query('Value/text()') AS Value

,XUV.XUV_VALUE AS VCReqId

,JPDRAFTITEM.DATA.query('ActId/text()') AS ActId

FROM dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN XUV

CROSS APPLY XUV.XUV_XML.nodes('JPDraft/JPDraftItems/JPDraftItem') JPDRAFTITEM(DATA)

WHERE XU.XUV_ID = XUV.XUV_ID

FOR XML PATH('JPDraftItem'), TYPE

)

FROM dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN XU

FOR XML PATH(''), ROOT('JPDraft'),TYPE

) AS XML_VAL

FROM dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN XU

)

UPDATE XUVFC

SET XUVFC.XUV_XML = BD.XML_VAL

FROM BASE_DATA BD

INNER JOIN dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN XUVFC

ON BD.XUV_ID = XUVFC.XUV_ID

;

SELECT

XU.XUV_ID

,XU.XUV_VALUE

,XU.XUV_XML

FROM dbo.TBL_XML_UPDATE_VALUE_FROM_COLUMN XU;

Output

<JPDraft>

<JPDraftId>0</JPDraftId>

<Purpose>To define business requirements.</Purpose>

<DateCreated>2016-04-25</DateCreated>

<JPDraftItem>

<ItemId>1</ItemId>

<Type>Skills</Type>

<Value>Ability.</Value>

<VCReqId>1007</VCReqId>

<ActId>67</ActId>

</JPDraftItem>

<JPDraftItem>

<ItemId>2</ItemId>

<Type>Skills</Type>

<Value>Difficulty</Value>

<VCReqId>1007</VCReqId>

<ActId>73</ActId>

</JPDraftItem>

<JPDraftItem>

<ItemId>3</ItemId>

<Type>Skills</Type>

<Value>Practical</Value>

<VCReqId>1007</VCReqId>

<ActId>63</ActId>

</JPDraftItem>

</JPDraft>

BigB SSC Eights! Points: 828 More actions · Answer 10

Thanks All. I was able to update XML column successfully. It was nice working with XML in SQL Server.

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 11

Eirikur Eiriksson

SSC Guru

Points: 182911

July 28, 2016 at 2:36 am

#1892420

You're very welcome,

and thanks for the feedback.

😎