Post reply

How to read an xml type into columns

  • October 20, 2010 at 5:08 am

    #241893

    Hi,

    I have a table in sqlserver 2005, where one of the column is a xml data type. I want to read the xml data type as a regular columns. For example the following is my xml saved, and I want to read as follows:

    <Product>

    <name>car</name>

    <color>red</color>

    <type>sedan</type>

    </Product>

    select name, color, type from producttable where productid= 5

    Thanks

  • October 20, 2010 at 1:56 pm

    #1239904

    You need to use XQuery.

    Side note: A great resource for several XQuery solutions is Jacob Sebastians blog

    DECLARE @tbl TABLE

    (

    id INT,

    col XML

    )

    INSERT INTO @tbl

    SELECT 1,'<Product>

    <name>car</name>

    <color>red</color>

    <type>sedan</type>

    </Product>' UNION ALL

    SELECT 2,'<Product>

    <name>car2</name>

    <color>blue</color>

    <type>rabbit</type>

    </Product>'

    SELECT

    id,

    T.c.value('name[1]','VARCHAR(30)') AS name,

    T.c.value('color[1]','VARCHAR(30)') AS color,

    T.c.value('type[1]','VARCHAR(30)') AS TYPE

    FROM @tbl tbl

    CROSS APPLY col.nodes('Product')T(c)

    WHERE id=2


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply