Confusion with XML

• Ext12

  Old Hand

  Points: 329

  More actions

  December 16, 2020 at 8:23 pm

  #3821346

  I really have not worked a lot with XML and I've only learned of CROSS APPLY in the last couple weeks, but I've googled a lot on trying to solve this and after a lot of time, I hope I could get some help with the query.

  I have data stored using XML tags in a column of a table.  I have been trying for quite a while now in a query from that column and other tables.  This is a pared down skeletal version of the format of the XML and the query.  The column type is XML.

  <data>

  <Company>

  <GROUP_ID ControlType="xxxxxxxxx" ParentName="ppppppppp" Value="100" />

  </Company>

  </data>

  This just will not pull the value "100" out no matter how I have tried.  All I get is a blank, but not a NULL.

  Is it because the data is not in a proper format or is my query just wrong?  The p_table has the XML column (U_Xml).

  select

  pt.id,

  x.y.value('(GROUP_ID)[1]', 'varchar(max)') AS [Group ID]

  From p_table pt

  CROSS APPLY pt.U_Xml.nodes('/data/Company') as x(y)

  Thanks for any direction I can receive.

  Bryon

  • This topic was modified 3 years, 4 months ago by  Ext12.

• ratbak

  Ten Centuries

  Points: 1333

  More actions

  December 16, 2020 at 9:20 pm

  #3821365

  GROUP_ID is a complex element composed of attributes. Value is an attribute of GROUP_ID, not an element.

  Try this:

  select
      pt.id,
      x.y.value('@Value','int') AS [Group ID]
  FROM p_table pt
  CROSS APPLY pt.U_Xml.nodes('data/Company/GROUP_ID') AS x(y);

• May 20, 2021 at 10:15 am

  This was removed by the editor as SPAM