I can not figure out how to parse the following XML statement which is contained in an XML defined field in my table tblSYS_AUDIT:
<AuditData>
<BusinessKey>
<Column Name="ACLT_ACCT" Type="int" Value="369568" />
</BusinessKey>
<ChangedData>
<Column Name="ACLT_AOA_X_REQ" Type="datetime" OldValue="" NewValue="12/31/2012 8:59:47 AM" />
<Column Name="ACLT_CHG_USER" Type="varchar" OldValue="cristina" NewValue="cristina" />
</ChangedData>
</AuditData>
I want the following two items from every record that has Column Name="ACLT_AOA_X_REQ"
1) The data in "NewValue" for only the line that has "ACLT_AOA_X_REQ",
2) The data from Value under the BusinessKey record which has the name "ACLT_ACCT".
I have tried the following, but I can't quite get it right:
1) select saud_delta.value('(AuditData/ChangedData/@Name[ACLT_AOA_X_REQ])', 'nvarchar(max)')
2) select * from tblSYS_AUDIT where saud_delta.value('(AuditData/BusinessKey)[1]', 'nvarchar(max)') like '%369568%'
Any help appreciated. Thank you. - richard
Open in new window
Produced this result:Open in new window
From this sample data:Open in new window