September 29, 2016 at 10:22 am
Hi there
I have a SQL table called dbo.AuditItem.
This contains a column called [OldRow]. This column is in XML format
I want to write a query to extract a piece of information from oldRow as follows:
<Airline airlineID="2"
airlineName="North Flying"
isVatRegistered="1"
vatNumber="12"
isVisaWaiver="0"
isWyvern="0"
departmentType="500"
officeLocation="1002"
updatedAt="2013-09-30T13:34:51.357"
latestVersion="17"
lastUpdatedByUserID="199"
isDeleted="0"
/>
Here I want to extract the value for lastUpdateByUserID
How would I do this ?
September 29, 2016 at 1:00 pm
chris.asaipillai-624309 (9/29/2016)
Hi thereI have a SQL table called dbo.AuditItem.
This contains a column called [OldRow]. This column is in XML format
I want to write a query to extract a piece of information from oldRow as follows:
<Airline airlineID="2"
airlineName="North Flying"
isVatRegistered="1"
vatNumber="12"
isVisaWaiver="0"
isWyvern="0"
departmentType="500"
officeLocation="1002"
updatedAt="2013-09-30T13:34:51.357"
latestVersion="17"
lastUpdatedByUserID="199"
isDeleted="0"
/>
Here I want to extract the value for lastUpdateByUserID
How would I do this ?
There are many ways to do this, here is one of them.
create table #Something
(
OldRow xml
)
insert #Something
select '<Airline airlineID="2"
airlineName="North Flying"
isVatRegistered="1"
vatNumber="12"
isVisaWaiver="0"
isWyvern="0"
departmentType="500"
officeLocation="1002"
updatedAt="2013-09-30T13:34:51.357"
latestVersion="17"
lastUpdatedByUserID="199"
isDeleted="0"
/>
'
select AirlineData.value('@lastUpdatedByUserID', 'int') as lastUpdatedByUserID
from #Something s
cross apply OldRow.nodes('/Airline') x(AirlineData)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 29, 2016 at 11:53 pm
As Sean rightly mentioned, there are many ways of parsing and extracting values from an XML. Many of those methods are similar in syntax but may vary hugely in performance. For a single element attribute extraction such as your posted example, using the value method directly on the column is by far the most efficient way.
😎
Single element attribute extraction ( using Sean's sample data )
SELECT
SX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]','INT') AS lastUpdatedByUserID
FROM #Something SX;
September 30, 2016 at 7:02 am
Eirikur Eiriksson (9/29/2016)
As Sean rightly mentioned, there are many ways of parsing and extracting values from an XML. Many of those methods are similar in syntax but may vary hugely in performance. For a single element attribute extraction such as your posted example, using the value method directly on the column is by far the most efficient way.😎
Single element attribute extraction ( using Sean's sample data )
SELECTSX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]','INT') AS lastUpdatedByUserID
FROM #Something SX;
Nice Eirikur. I don't wrestle with xml too often. Thanks for teaching me a better way to deal with this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2016 at 7:14 am
Sean Lange (9/30/2016)
Eirikur Eiriksson (9/29/2016)
As Sean rightly mentioned, there are many ways of parsing and extracting values from an XML. Many of those methods are similar in syntax but may vary hugely in performance. For a single element attribute extraction such as your posted example, using the value method directly on the column is by far the most efficient way.😎
Single element attribute extraction ( using Sean's sample data )
SELECTSX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]','INT') AS lastUpdatedByUserID
FROM #Something SX;
Nice Eirikur. I don't wrestle with xml too often. Thanks for teaching me a better way to deal with this.
You are most welcome Sean.
😎
October 2, 2016 at 2:59 am
Thanks guys for that.it worked.
OK if I need to extract another item from that pice of xml then do I write this folowing
SELECT
SX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]','INT') AS lastUpdatedByUserID,
SX.OldRow.value('(Airline/@UpdatedAt)[1]','DATETIME') AS lastUpdatedAt,
FROM #Something SX;[/quote]
October 2, 2016 at 4:44 am
chris.asaipillai-624309 (10/2/2016)
Thanks guys for that.it worked.OK if I need to extract another item from that pice of xml then do I write this folowing
SELECTSX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]','INT') AS lastUpdatedByUserID,
SX.OldRow.value('(Airline/@UpdatedAt)[1]','DATETIME') AS lastUpdatedAt,
FROM #Something SX;
That is correct, just add a value call for each of the desired attribute values you need to extract.
😎
Example of full extraction from the sample data
SELECT
SX.OldRow.value('(Airline/@airlineID)[1]' ,'INT' ) AS airlineID
,SX.OldRow.value('(Airline/@airlineName)[1]' ,'VARCHAR(50)' ) AS airlineName
,SX.OldRow.value('(Airline/@isVatRegistered)[1]' ,'TINYINT' ) AS isVatRegistered
,SX.OldRow.value('(Airline/@vatNumber)[1]' ,'INT' ) AS vatNumber
,SX.OldRow.value('(Airline/@isVisaWaiver)[1]' ,'TINYINT' ) AS isVisaWaiver
,SX.OldRow.value('(Airline/@isWyvern)[1]' ,'TINYINT' ) AS isWyvern
,SX.OldRow.value('(Airline/@departmentType)[1]' ,'INT' ) AS departmentType
,SX.OldRow.value('(Airline/@officeLocation)[1]' ,'INT' ) AS officeLocation
,SX.OldRow.value('(Airline/@updatedAt)[1]' ,'DATETIME' ) AS updatedAt
,SX.OldRow.value('(Airline/@latestVersion)[1]' ,'INT' ) AS latestVersion
,SX.OldRow.value('(Airline/@lastUpdatedByUserID)[1]' ,'INT' ) AS lastUpdatedByUserID
,SX.OldRow.value('(Airline/@isDeleted)[1]' ,'TINYINT' ) AS isDeleted
FROM #Something SX;
Output
airlineID airlineName isVatRegistered vatNumber isVisaWaiver isWyvern departmentType officeLocation updatedAt latestVersion lastUpdatedByUserID isDeleted
----------- -------------- --------------- ----------- ------------ -------- -------------- -------------- ----------------------- ------------- ------------------- ---------
2 North Flying 1 12 0 0 500 1002 2013-09-30 13:34:51.357 17 199 0
Here is an alternative method for extracting attribute values from single element XML snippets, comes in very handy if the structure is either inconsistent or unknown
SELECT
ATTRIB.DATA.value('local-name(.)' ,'VARCHAR(50)') AS AttributeName
,ATTRIB.DATA.value('.' ,'VARCHAR(50)') AS AttributeValue
FROM #Something SX
CROSS APPLY SX.OldRow.nodes('//@*') ATTRIB(DATA);
Output
AttributeName AttributeValue
--------------------- ------------------------
airlineID 2
airlineName North Flying
isVatRegistered 1
vatNumber 12
isVisaWaiver 0
isWyvern 0
departmentType 500
officeLocation 1002
updatedAt 2013-09-30T13:34:51.357
latestVersion 17
lastUpdatedByUserID 199
isDeleted 0
October 3, 2016 at 4:41 am
That's brilliant and works for me . Thanks so much guys for your help on this.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply