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 )
SELECT
SX.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 )
SELECT
SX.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
SELECT
SX.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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy