Extract attribute value from XML

Question

I have data stored using XML tags in a column of a table. I am trying to pull data in a query from that column. This is a pared down skeletal version of the format of the XML and the query for a single node. 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.

Answer 1

GROUP_ID element has no value, i.e. child text node. It has just attributes.

Please try the following.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (id INT IDENTITY PRIMARY KEY, xmldata XML);
INSERT INTO @tbl (xmldata) VALUES
(N'<data>
    <Company>
        <GROUP_ID ControlType="xxxxxxxxx" ParentName="ppppppppp" Value="100"/>
    </Company>
</data>');
-- DDL and sample data population, end

SELECT id
    , c.value('@ControlType', 'VARCHAR(30)') AS ControlType
    , c.value('@ParentName', 'VARCHAR(30)') AS ParentName
    , c.value('@Value', 'INT') AS [Value]
FROM @tbl CROSS APPLY xmldata.nodes('/data/Company/GROUP_ID') AS t(c);

Output

+----+-------------+------------+-------+
| id | ControlType | ParentName | Value |
+----+-------------+------------+-------+
|  1 | xxxxxxxxx   | ppppppppp  |   100 |
+----+-------------+------------+-------+