Post reply

XML data extract

  • September 17, 2021 at 10:06 am

    #3930474

    Hi

    I've been tasked with extracting information for a report where the field type is XML.

    THe SQL table contains 2 fields Personcode and XMLBody

    THe XMLBody Field contains information like the below example;

    <content>

    <fieldgroup>

    <field id="Comment" type="textbox" title="Comment:" rows="6" cols="50" cssclass="textbox" mandatory="1">Extarct the data string located here.</field>

    <field id="Action" type="textbox" title="Action:" rows="6" cols="50" cssclass="textbox">more test text.</field>

    <field id="Outcome" type="textbox" title="Outcome:" rows="6" cols="50" cssclass="textbox" />

    </fieldgroup>

    </content>

    Can anyone help/explain the SQL to extract from the XML  where the field id node = "Comment" and  then only pull back the comment text e.g. the sql output would read;

    Personcode   |  XML Text

    99999            | Extract the data string located here

    Any regarding SQL and XML data extraction would be greatly appreciated.

    Thank you

  • September 17, 2021 at 10:18 am

    #3930475

    Try this

    declare @MyTable table(Personcode int, XMLBody XML);

    insert into @MyTable(Personcode, XMLBody)
    values(99999,'<content>
    <fieldgroup>
    <field id="Comment" type="textbox" title="Comment:" rows="6" cols="50" cssclass="textbox" mandatory="1">Extract the data string located here.</field>
    <field id="Action" type="textbox" title="Action:" rows="6" cols="50" cssclass="textbox">more test text.</field>
    <field id="Outcome" type="textbox" title="Outcome:" rows="6" cols="50" cssclass="textbox" />
    </fieldgroup>
    </content>');

    select Personcode,
           XMLBody.value('(/content/fieldgroup/field[@id="Comment"])[1]','varchar(100)') as [XML Text]
    from @MyTable;

    ____________________________________________________

    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

  • September 17, 2021 at 10:35 am

    #3930480

    This worked perfectly

    Thank you for your help and time.

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

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