Post reply

Retrieve the information from XML column

  • May 1, 2011 at 11:13 pm

    #239388

    Hello,

    I would like to retrieve the information from XML column.

    XML sample is like:

    <Data>

    <Property Name = "StudentId" Value ="1" />

    <Property Name = "StudentBirthDate" Value ="01-01-1988" />

    <Property Name = "StudentBloodGroup" Value ="O-" />

    </Data>

    I want to generate the output like:

    StudentId StudentBirthDate StudentBloodGroup

    1 01-01-1988 O-

    How can I generate this type of output?

    sample script:

    DECLARE @data TABLE

    (

    StudentId INT,

    StudentExtraInfo XML

    )

    insert into @data

    values (1,

    '<Data>

    <Property Name = "StudentId" Value ="1" />

    <Property Name = "StudentBirthDate" Value ="01-01-1988" />

    <Property Name = "StudentBloodGroup" Value ="O-" />

    </Data>

    '),

    (2,

    '<Data>

    <Property Name = "StudentId" Value ="2" />

    <Property Name = "StudentBirthDate" Value ="10-10-2008" />

    <Property Name = "StudentBloodGroup" Value ="O+" />

    </Data>

    ')

    select * from @data

    Thanks in advance.

    Thanks

  • May 2, 2011 at 2:18 pm

    #1318722

    Depending on the Server version you're using you could either use OpenXML or XQuery.

    You might want to have a look at Jacob Sebastians blog

    If you can't find a solution please post back. I'm sorry for just pointing you to a blog instead of providing a solution but it looks like a homework question...


    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]

  • May 2, 2011 at 11:55 pm

    #1318884

    I have referred 'http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-lab-1-transforming-rows-to-columns.aspx' link, and able to solve it.

    I have the data like sample XML which I added in the question.

    Table may contain 1 lac rows - all (or few) contain the other information with the XML data column. If I use the query mentioned in the above link then does it create any performance issue? if yes, how can I optimize the query for better performance?

    Thanks

  • May 3, 2011 at 11:07 am

    #1319249

    It shouldn't cause a performance issue since the XML structure is neither complicated nor large in size.

    The best way to know is to test it. 😉


    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 4 posts - 1 through 3 (of 3 total)

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