XML data extract

• dbayliss1978

  SSC Enthusiast

  Points: 178

  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

• Mark Cowne

  One Orange Chip

  Points: 26981

  More actions

  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

• dbayliss1978

  SSC Enthusiast

  Points: 178

  More actions

  September 17, 2021 at 10:35 am

  #3930480

  This worked perfectly

  Thank you for your help and time.