Home Forums Programming XML Extracting XML RE: Extracting XML

  • November 11, 2013 at 3:45 pm

    #1665601

    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)

    Idkeyvalue

    41Titletext1

    41Result90

    42Titletext2

    42Result150etc.

    and I would like to get it to form:

    Idkeyvaluekey2value2

    41Titletext1Result90

    42Titletext2Result150 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...

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001