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
Microsoft SQL Server

Avatar of undefined
Last Comment
richardandro

8/22/2022 - Mon
PortletPaul

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

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

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

ASKER
Close enough, really helps me learn how to understand it and hence change it to my needs. Thank you.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck