using XML method

  • DECLARE @xml AS XML =

    '<Root>

    <SubNode>

    <Property1>Value 1</Property1>

    <Property2>Value 2</Property2>

    <Property3>Value 3</Property3>

    </SubNode>

    </Root>'

    Different options to retrive Value 1, Value 2, ... in rows.

    Abhijit - http://abhijitmore.wordpress.com

  • Since you use different tags, I think that you can do it only with union all query. If you can modify the XML so the tag’s name will be the same all the time (for example Property instead of Property1, Property2…PropertyN), then it will be much easier. In any case here is a way of doing it:

    set @xml =

    '<Root>

    <SubNode>

    <Property1>Value 1</Property1>

    <Property2>Value 2</Property2>

    <Property3>Value 3</Property3>

    </SubNode>

    </Root>'

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property1') tbl (col)

    union all

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property2') tbl (col)

    union all

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property3') tbl (col)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Adi Cohn-120898 (1/10/2011)


    Since you use different tags, I think that you can do it only with union all query. If you can modify the XML so the tag’s name will be the same all the time (for example Property instead of Property1, Property2…PropertyN), then it will be much easier. In any case here is a way of doing it:

    set @xml =

    '<Root>

    <SubNode>

    <Property1>Value 1</Property1>

    <Property2>Value 2</Property2>

    <Property3>Value 3</Property3>

    </SubNode>

    </Root>'

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property1') tbl (col)

    union all

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property2') tbl (col)

    union all

    select tbl.col.value(('.'),'varchar(10)')

    from @xml.nodes('Root/SubNode/Property3') tbl (col)

    Adi

    Also this

    select tbl.col.value(('.[1]'),'varchar(10)')

    from @xml.nodes('/Root/SubNode/*') tbl (col)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • Also this

    select tbl.col.value(('.[1]'),'varchar(10)')

    from @xml.nodes('/Root/SubNode/*') tbl (col)

    Very nice. Learned something new today:-)

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • very useful, I too learned something new today.

Viewing 5 posts - 1 through 4 (of 4 total)

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