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/