CODEWITHSUNDEEP
sqlsql-serverxml
POSH Guy
POSH Guy

Reputation: 2048

Extract attribute details from XML data in SQL

I have a column (attributes) in SQL which contains XML data. Could you please advise how to extract a Errors details from that XML data

enter image description here

I tried the below query it didn't work.

SELECT top 10 identity_name,
CAST (tr.attributes as xml).value('(Attributes/Map/entry/value/accountrequest/ProvisioningResult/Errors[@key="Message"])[1]/@value','nvarchar(200)') as Message
From [identityiq].[identityiq].[spt_provisioning_transaction] as tr 
where id = '8aae1d866d49a4b2016d57852fc3503b'

XML data is below

<Attributes>   <Map>     <entry key="accessRequestId" value="0000086661"/>     <entry key="request">       <value>         <AccountRequest application="Active Directory" nativeIdentity="123" op="Modify">           <Attributes>             <Map>               <entry key="provisioningTransactionId" value="8aae1d866cace49c016cf7e26a7f4a8a"/>             </Map>           </Attributes>           <AttributeRequest name="memberOf" op="Remove" value="Testgroup"/>           <ProvisioningResult>             <Errors>               <Message key="Errors returned from IQService. &quot;Failed to update attributes for identity 123. Failed to connect to the server for Testgroup&quot;" type="Error"/>             </Errors>           </ProvisioningResult>         </AccountRequest>       </value>     </entry>   </Map> </Attributes>

Upvotes: 0

Views: 207

Answers (1)

lptr
lptr

Reputation: 6788

pay attention to case sensitivity..

declare @x xml = N'
<Attributes>
  <Map>
    <entry key="accessRequestId" value="0000086661" />
    <entry key="request">
      <value>
        <AccountRequest application="Active Directory" nativeIdentity="123" op="Modify">
          <Attributes>
            <Map>
              <entry key="provisioningTransactionId" value="8aae1d866cace49c016cf7e26a7f4a8a" />
            </Map>
          </Attributes>
          <AttributeRequest name="memberOf" op="Remove" value="Testgroup" />
          <ProvisioningResult>
            <Errors>
              <Message key="Errors returned from IQService. &quot;Failed to update attributes for identity 123. Failed to connect to the server for Testgroup&quot;" type="Error" />
            </Errors>
          </ProvisioningResult>
        </AccountRequest>
      </value>
    </entry>
  </Map>
</Attributes>
';

    select @x.value('(Attributes/Map/entry/value/AccountRequest/ProvisioningResult/Errors/Message[@type="Error"])[1]/@key','nvarchar(200)') as Message;

Upvotes: 1

Related Questions