XML data extract

Question

XML data extract

dbayliss1978

SSC Rookie

Points: 33
More actions
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

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

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

Mark Cowne One Orange Chip Points: 26826 More actions · Answer 1

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

dbayliss1978 SSC Rookie Points: 33 More actions · Answer 2

This worked perfectly

Thank you for your help and time.