September 17, 2021 at 10:06 am
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
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/61537September 17, 2021 at 10:35 am
This worked perfectly
Thank you for your help and time.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy