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, 5 months ago by Ext12.