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!
Advertisement
Answer
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 | +-----------------------+