How to extract a value from XML column in a SQL Table

  • 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 ?

  • chris.asaipillai-624309 (9/29/2016)


    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 ?

    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/

  • 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;

  • 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/

  • 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.

    😎

  • 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]

  • 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

  • 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