? on parsing XML field

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  April 22, 2015 at 3:06 pm

  #296156

  Hi

  I've looked this up and having a hard time understanding how this works

  I want to parse out an XML field (field name Data) on a table an example is ..

  <Data>

  <New_x0020_Issues_x0020_Presented_x003F_>false</New_x0020_Issues_x0020_Presented_x003F_>

  <Present>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Client</Name>

  <Checked>true</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Spouse of Significant Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Mother</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Father</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Sibling</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Children</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  </Present>

  </Data>

  table name is xdocuments

  I wanted to parse Name and Checked

  Thanks

  Joe

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 22, 2015 at 3:28 pm

  #1792871

  -- a db we both have

  USE tempdb

  GO

  -- create your table

  IF OBJECT_ID('tempdb.dbo.xdocuments') IS NOT NULL DROP TABLE dbo.xdocuments

  GO

  CREATE TABLE dbo.xdocuments(xid int not null, yourxmlcolumn xml not null);

  GO

  -- insert a couple records

  INSERT dbo.xdocuments

  SELECT 1,

  '<Data>

  <New_x0020_Issues_x0020_Presented_x003F_>false</New_x0020_Issues_x0020_Presented_x003F_>

  <Present>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Client</Name>

  <Checked>true</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Spouse of Significant Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Mother</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Father</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Sibling</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Children</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  </Present>

  </Data>

  '

  UNION ALL

  SELECT 2,

  '

  <Data>

  <New_x0020_Issues_x0020_Presented_x003F_>false</New_x0020_Issues_x0020_Presented_x003F_>

  <Present>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Client</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Spouse of Significant Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Mother</Name>

  <Checked>true</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Father</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Sibling</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Children</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  <Name>Other</Name>

  <Checked>false</Checked>

  </FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option>

  </Present>

  </Data>

  ';

  SELECT

  xid,

  name = xx.value('(Name/text())[1]','varchar(10)'),

  checked = xx.value('(Checked/text())[1],','varchar(5)')

  FROM dbo.xdocuments

  CROSS APPLY yourxmlcolumn.nodes('Data/Present/FST_x005F_x0020_Progress_x005F_x0020_Note_Present_Option') x(xx);

  edit: added a second record and added xid to show how you determine which record you are looking at.

  "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

• jbalbo

  SSCertifiable

  Points: 7583

  More actions

  April 22, 2015 at 3:32 pm

  #1792872

  Thanks Alan,

  I need to do a little reading on cross apply.

  I really want to usndersand how this works!!! 🙂

  Thanks

  Again

• Alan Burstein

  SSC Guru

  Points: 61141

  More actions

  April 22, 2015 at 3:43 pm

  #1792873

  Read Paul Whites articles about APPLY, they are the best out there IMHO. These are the two article that really helped me understand APPLY:

  Understanding and Using APPLY (Part 1)[/url]

  Understanding and Using APPLY (Part 2)[/url]

  Then, once you have a grasp on APPLY, you need to understand the T-SQL XML value() and nodes() methods. Robert Sheldon has a pretty good article about these here: The XML Methods in SQL Server[/url]. There's also the MSDN articles: xml Data Type Methods.

  If you don't understand XML see the W3Schools page: XML Tutorial

  "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