Parsing XML field in MS SQL SERVER

richardandro
richardandro used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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

Author

Commented:
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?
EE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
I cannot replicate the problem, this is the result I get using your modified query: 
|  AcctName | AcctType | AcctValue |       DataName | DataType |             DataValue |
|-----------|----------|-----------|----------------|----------|-----------------------|
| ACLT_ACCT |      int |    369568 | ACLT_AOA_X_REQ | datetime | 12/31/2012 8:59:47 AM |

Open in new window

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
richardandro

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial