Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Extracting XML Expand / Collapse
Author
Message
Posted Saturday, November 9, 2013 5:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, November 11, 2013 10:03 AM
Points: 2, Visits: 6
I know this is not a new issue but:
How can I extract data from XML column:
I have DB1 and Table1 and Column1. In Column1 I have XML data: <instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</ Result ></instance>
Now I want to query two values from that column: <Title> and <Result> to make an diagram.
How can I do this?
Post #1512900
Posted Saturday, November 9, 2013 8:32 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 1,785, Visits: 5,678
declare @Table1 table(Column1 xml);

insert @Table1(Column1)
values( '<instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</Result ></instance>');

select
Column1.value('(instance/Title/text())[1]','varchar(255)') as title
, Column1.value('(instance/Result/text())[1]','varchar(255)') as result
from @Table1



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1512913
    Posted Monday, November 11, 2013 10:01 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, November 11, 2013 10:03 AM
    Points: 2, Visits: 6
    Thanks MM for your fast reply!
    However I didn’t get it to work. Maybe problem is in Column1 data type, it is ntext. I read somewhere that it couldn’t be ntext type in declare procedure. Is it so?
    I have another way to do it: just have to merge two rows because now it is like that: (Ids are same in two pair rows)
    Id key value
    41 Title text1
    41 Result 90
    42 Title text2
    42 Result 150 etc.
    and I would like to get it to form:
    Id key value key2 value2
    41 Title text1 Result 90
    42 Title text2 Result 150 etc.
    Do you have any ideas for this.
    Post #1513193
    Posted Monday, November 11, 2013 3:45 PM


    Mr or Mrs. 500

    Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

    Group: General Forum Members
    Last Login: 2 days ago @ 4:49 AM
    Points: 556, Visits: 2,581
    timvil (11/11/2013)
    Thanks MM for your fast reply!
    However I didn’t get it to work. Maybe problem is in Column1 data type, it is ntext. I read somewhere that it couldn’t be ntext type in declare procedure. Is it so?


    You cannot declare a variable as ntext. If column1 is ntext, to get it working using mm's code you would do this:

    declare @Table1 table(Column1 ntext);

    insert @Table1(Column1)
    values( '<instance><Title>New product</Title> <Date> 2013.10.24</Date><Author>TV</Author><Meter>LV</Meter><Result>90</Result ></instance>');

    WITH ntext_to_xml(Column1) AS (SELECT CAST(Column1 AS xml) FROM @Table1)
    select
    Column1.value('(instance/Title/text())[1]','varchar(255)') as title
    , Column1.value('(instance/Result/text())[1]','varchar(255)') as result
    from ntext_to_xml

    I have another way to do it: just have to merge two rows because now it is like that: (Ids are same in two pair rows)
    Id key value
    41 Title text1
    41 Result 90
    42 Title text2
    42 Result 150 etc.
    and I would like to get it to form:
    Id key value key2 value2
    41 Title text1 Result 90
    42 Title text2 Result 150 etc.
    Do you have any ideas for this.


    This looks simple but do you have any sample data? This information does not exist in the sample data that you provided...


    -- Alan Burstein



    Read this article for best practices on asking questions.
    Need to split a string? Try this (Jeff Moden)
    Need a pattern-based string spitter? Try this (Dwain Camps)
    My blog
    Post #1513283
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse