SQL - Cast Column as XML then Query Value from said column

Question

I have a column with a bunch of unformatted XML code. I am trying to really just query 1 value out of the column. The value inside of the column is listed below:

<Attributes>
  <Map>
    <entry key="assignmentId" value="28df90bef6c44cf18a0da10bb12c2d66" />
    <entry key="attachmentConfigList" />
    <entry key="attachments" />
    <entry key="flow" value="AccessRequest" />
    <entry key="id" value="0a58be7a773a1abf81774a9ef2ce444a" />
    <entry key="identityEntitlementId" value="0a58be7877b1137e8177b61f500d0792" />
    <entry key="interface" value="LCM" />
    <entry key="managedAttributeType" value="Entitlement" />
    <entry key="operation" value="EntitlementAdd" />
    <entry key="provisioningPlan">
      <value>
        <ProvisioningPlan trackingId="226a73cd2a8642abadabd1e00c752983">
          <AccountRequest application="Active Directory" nativeIdentity="CN=Turtle\, Cecil" op="Modify" targetIntegration="Active Directory">
            <Attributes>
              <Map>
                <entry key="attachmentConfigList" />
                <entry key="attachments" />
                <entry key="flow" value="AccessRequest" />
                <entry key="id" value="0a58be7a773a1abf81774a9ef2ce444a" />
                <entry key="interface" value="LCM" />
                <entry key="operation" value="Add" />
                <entry key="provisioningMetaData">
                  <value>
                    <Map>
                      <entry key="entitlementAttributes">
                        <value>
                          <Map>
                            <entry key="memberOf">
                              <value>
                                <Map>
                                  <entry key="ReadItem" />
                                </Map>
                              </value>
                            </entry>
                          </Map>
                        </value>
                      </entry>
                      <entry key="linkAttributes">
                        <value>
                          <Map>
                            <entry key="distinguishedName" value="CN=Turtle\, Cecil" />
                          </Map>
                        </value>
                      </entry>
                    </Map>
                  </value>
                </entry>
                <entry key="requester" value="100051" />
                <entry key="requesterComments" value="Mr. Turtle requires access." />
              </Map>
            </Attributes>
            <AttributeRequest assignmentId="28df90bef6c44cf18a0da10bb12c2d66" displayValue="NSharePoint Read Item" name="memberOf" op="Add" trackingId="226a73cd2a8642abadabd1e00c752983" value="2">
              <Attributes>
                <Map>
                  <entry key="assignment" value="true" />
                  <entry key="comments" value="Mr. Turtle requires access." />
                </Map>
              </Attributes>
            </AttributeRequest>
          </AccountRequest>
          <Attributes>
            <Map>
              <entry key="source" value="LCM" />
            </Map>
          </Attributes>
        </ProvisioningPlan>
      </value>
    </entry>
    <entry key="requesterComments" value="Mr. Turtle requires access." />
  </Map>
</Attributes>

The value that I am looking for is displayValue="NSharePoint Read Item" which is located in the line:

<AttributeRequest assignmentId="28df90bef6c44cf18a0da10bb12c2d66" displayValue="NSharePoint Read Item" name="memberOf" op="Add" trackingId="226a73cd2a8642abadabd1e00c752983" value="2">

I have the following query:

  ;WITH XMLNAMESPACES 
('http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/IndividualSurvey' AS ns)
  select 
    (cast(attributes as xml)).value('(/ns:attributes/ns:map/ns:value)[1]', 'varchar(max)') 
  from [identityiq].[identityiq].[spt_identity_request_item]
  where id = '0a58be7877b1137e8177b5f3958a0740'

which I have been using to try and pull any value out of the column, but everything is returning NULL, regardless of what I try.

Looking for any help, thank you!

Answer 1

Please try the following solution. It is for MS SQL Server.

Amazingly enough, the provided XML has no namespaces. Though it was an attempt to use a namespace.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, xmldata NVARCHAR(MAX));
INSERT INTO @tbl VALUES 
(N'<Attributes>
    <Map>
        <entry key="assignmentId" value="28df90bef6c44cf18a0da10bb12c2d66"/>
        <entry key="attachmentConfigList"/>
        <entry key="attachments"/>
        <entry key="flow" value="AccessRequest"/>
        <entry key="id" value="0a58be7a773a1abf81774a9ef2ce444a"/>
        <entry key="identityEntitlementId"
               value="0a58be7877b1137e8177b61f500d0792"/>
        <entry key="interface" value="LCM"/>
        <entry key="managedAttributeType" value="Entitlement"/>
        <entry key="operation" value="EntitlementAdd"/>
        <entry key="provisioningPlan">
            <value>
                <ProvisioningPlan trackingId="226a73cd2a8642abadabd1e00c752983">
                    <AccountRequest application="Active Directory"
                                    nativeIdentity="CN=Turtle\, Cecil"
                                    op="Modify"
                                    targetIntegration="Active Directory">
                        <Attributes>
                            <Map>
                                <entry key="attachmentConfigList"/>
                                <entry key="attachments"/>
                                <entry key="flow" value="AccessRequest"/>
                                <entry key="id"
                                       value="0a58be7a773a1abf81774a9ef2ce444a"/>
                                <entry key="interface" value="LCM"/>
                                <entry key="operation" value="Add"/>
                                <entry key="provisioningMetaData">
                                    <value>
                                        <Map>
                                            <entry key="entitlementAttributes">
                                                <value>
                                                    <Map>
                                                        <entry key="memberOf">
                                                            <value>
                                                                <Map>
                                                                    <entry key="ReadItem"/>
                                                                </Map>
                                                            </value>
                                                        </entry>
                                                    </Map>
                                                </value>
                                            </entry>
                                            <entry key="linkAttributes">
                                                <value>
                                                    <Map>
                                                        <entry key="distinguishedName"
                                                               value="CN=Turtle\, Cecil"/>
                                                    </Map>
                                                </value>
                                            </entry>
                                        </Map>
                                    </value>
                                </entry>
                                <entry key="requester" value="100051"/>
                                <entry key="requesterComments"
                                       value="Mr. Turtle requires access."/>
                            </Map>
                        </Attributes>
                        <AttributeRequest assignmentId="28df90bef6c44cf18a0da10bb12c2d66"
                                          displayValue="NSharePoint Read Item"
                                          name="memberOf" op="Add"
                                          trackingId="226a73cd2a8642abadabd1e00c752983"
                                          value="2">
                            <Attributes>
                                <Map>
                                    <entry key="assignment" value="true"/>
                                    <entry key="comments"
                                           value="Mr. Turtle requires access."/>
                                </Map>
                            </Attributes>
                        </AttributeRequest>
                    </AccountRequest>
                    <Attributes>
                        <Map>
                            <entry key="source" value="LCM"/>
                        </Map>
                    </Attributes>
                </ProvisioningPlan>
            </value>
        </entry>
        <entry key="requesterComments" value="Mr. Turtle requires access."/>
    </Map>
</Attributes>');
-- DDL and sample data population, end

SELECT TRY_CAST(xmldata AS XML)
    .value('(/Attributes/Map/entry/value/ProvisioningPlan/AccountRequest/AttributeRequest/@displayValue)[1]', 'VARCHAR(100)') AS displayValue
FROM @tbl;

Output

+-----------------------+
|     displayValue      |
+-----------------------+
| NSharePoint Read Item |
+-----------------------+