How do I extract a specific ID in SQL from a XML column?

Question

I have looked at other posts on here but I and still struggling to make it work. For example, here is my XML and I am trying retrieve just the RoleID:

<?xml version="1.0" encoding="utf-16"?>
<C_ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" id="aaaaaaaa-1111-1111-1111-aaaaaaaaaaaa" t_="Name" a_="U">
    <U_ />
    <S_>
        <C_ id="bbbbbbbb-2222-2222-2222-bbbbbbbbbbbb" t_="NameMembership" a_="C">
            <U_>
                <F_ n_="NameId" d_="UI">
                    <N_>cccccccc-3333-3333-ccccccccccccccccc</N_>
                </F_>
                <F_ n_="RoleId" d_="UI">
                    <N_>dddddddd-4444-4444-ddddddddddddddddd</N_>
                </F_>
            </U_>
            <S_ />
        </C_>
    </S_>
</C_>

To confirm, I am looking to retrieve the value

dddddddd-4444-4444-ddddddddddddddddd.

Any help here would be very much appreciated. FYI I am using SQL Server 2012

Answer 1

If you have this XML in a SQL Server variable like this:

DECLARE @SomeVar XML = N'....(your XML here)....';

then you can fetch the value of the node with the n_="RoleId" with this SQL/XQuery:

SELECT
    @SomeVar.value('(/C_/S_/C_/U_/F_[@n_="RoleId"])[1]', 'varchar(100)')

UPDATE: if you have an XML column in a table, you'd approach it like this:

DECLARE @xmlTable TABLE (ID INT NOT NULL, XmlData XML);

INSERT INTO @xmlTable (ID, XmlData)
VALUES (1, N'----your XML here----');

SELECT
    XC.value('(F_[@n_="RoleId"])[1]', 'varchar(100)')
FROM
    @value v 
CROSS APPLY
    v.XmlData.nodes('/C_/S_/C_/U_') AS XT(XC)
WHERE
    v.ID = 1