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

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

| AcctName | AcctType | AcctValue | DataName | DataType | DataValue | |-----------|----------|-----------|----------------|----------|-----------------------| | ACLT_ACCT | int | 369568 | ACLT_AOA_X_REQ | datetime | 12/31/2012 8:59:47 AM |

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 51

PortletPaulEE Topic AdvisorCommented: 2015-09-15

richardandroAuthor Commented: 2015-09-16

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?

PortletPaulEE Topic AdvisorCommented: 2015-09-16

I cannot replicate the problem, this is the result I get using your modified query:

see http://sqlfiddle.com/#!6/7906b/16

Hence I cannot suggest anything of note because the SQL looks fine, and works well for me.

I think the data you and I are using may be different

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

richardandroAuthor Commented: 2015-09-17

Close enough, really helps me learn how to understand it and hence change it to my needs. Thank you.

Parsing XML field in MS SQL SERVER

Who is Participating?