Link to home
Start Free TrialLog in
Avatar of richardandro
richardandro

asked on

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

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

Avatar of richardandro
richardandro

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
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Close enough, really helps me learn how to understand it and hence change it to my needs. Thank you.