Parsing XML field in MS SQL SERVER

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

Microsoft SQL Server

Last Comment

richardandro

8/22/2022 - Mon

PortletPaul

9/15/2015

This query:

SELECT
       [Name] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Name)[1]', 'nvarchar(50)')
     , [Type] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Type)[1]', 'nvarchar(50)')
     , [Value] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Value)[1]', 'int')
FROM tblSYS_AUDIT ta
WHERE
       ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Value)[1]', 'int') = 369568
;

Open in new window

Produced this result:

|      Name | Type |  Value |
|-----------|------|--------|
| ACLT_ACCT |  int | 369568 |

Open in new window

From this sample data:

CREATE TABLE tblSYS_AUDIT
    ([saud_delta] xml)
;

INSERT INTO tblSYS_AUDIT
    ([saud_delta])
VALUES
  (N'<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>
');

Open in new window

richardandro

9/16/2015

ASKER

Almost there. I want to also get the Name and NewValue from the ChangedData and I tried this but it doesn't quite do it. (I had to change int to nvarchar(50) because of I received an error proabably due to some weird data).

SELECT top 1
[AcctName] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Name)[1]', 'nvarchar(50)')
, [AcctType] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Type)[1]', 'nvarchar(50)')
, [AcctValue] = ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Value)[1]', 'nvarchar(50)')
, [DataName] = ta.saud_delta.value('(/AuditData/ChangedData/Column/@Name)[1]', 'nvarchar(50)')
, [DataType] = ta.saud_delta.value('(/AuditData/ChangedData/Column/@Type)[1]', 'nvarchar(50)')
, [DataValue] = ta.saud_delta.value('(/AuditData/ChangedData/Column/@NewValue)[1]', 'nvarchar(50)')
FROM tblSYS_AUDIT ta
WHERE
ta.saud_delta.value('(/AuditData/BusinessKey/Column/@Value)[1]', 'nvarchar(50)') = '369568'
;

I received the results: ACLL_ACCT int 369568 ACLL_ACCT int NULL

What do you suggest?

ASKER CERTIFIED SOLUTION

PortletPaul

9/16/2015

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

richardandro