Post reply

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

  • September 29, 2016 at 10:22 am

    #330962

    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

    #1904026

    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/

  • September 29, 2016 at 11:53 pm

    #1904086

    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

    #1904141

    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

    #1904144

    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

    #1904301

    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

    #1904302

    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

    #1904361

    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

Cookies on SQLServerCentral

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