Confusion with XML

  • 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 1 month ago by  Ext12.
  • 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);

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply