Parse delimited string into XML data

Question

Parse delimited string into XML data

robbase9

Mr or Mrs. 500

Points: 517
More actions
March 9, 2011 at 3:56 pm

#246714

I have a table that holds audit data. For every row that's inserted, updated, or deleted into the database, a row gets added to the AuditLog table.
The data is stored in a pipe-delimited column. I need to parse the delimited string into XML. I need to get the column names from the table that the data
was inserted into, and use those as the attribute columns of the XML data.
Here is a little example using two different tables, L_APPCATTYPES and M_APPCATTYPES. There are over 100 tables worth of data stored in the AuditLog table.
CREATE TABLE [dbo].[AuditLog](
[AuditPK] [int] NOT NULL,
[TableName] [varchar](128) NOT NULL,
[ActionType] [char](1) NOT NULL,
[BeforeImage] [varchar](2000) NULL)
GO
CREATE TABLE [dbo].[L_APPCATTYPES](
[CATTYPE] [numeric](2, 0) NOT NULL,
[CATCODE] [varchar](10) NOT NULL,
[CATDESC] [varchar](50) NOT NULL,
[CATRANK] [numeric](2, 0) NULL,
[OBJECTTYPE] [numeric](2, 0) NOT NULL)
GO
CREATE TABLE [dbo].[M_APPCATTYPES](
[CATCODE] [varchar](50) NOT NULL,
[CATTYPE] [numeric](2, 0) NOT NULL)
GO
insert into AuditLog([AuditPK],[TableName],[ActionType],[BeforeImage])values(1,'L_APPCATTYPES','I','1|UNSPEC|UNDERSPECIFIED SOURCE VALUE||-1')
insert into AuditLog([AuditPK][TableName],[ActionType],[BeforeImage])values(2,'M_APPCATTYPES','I','PANTS|1')
insert into L_APPCATTYPES([CATTYPE],[CATCODE],[CATDESC],[CATRANK],[OBJECTTYPE])values(1,'UNSPEC','AMBIGUOUS SOURCE VALUE',NULL,-1)
insert into M_APPCATTYPES([CATCODE],[CATTYPE]) values('PANTS',1)
I want to update the BeforeImage data in the Auditlog table to XML. But I need the column names as the attributes of the XML.
So the following queries would return the following:
SELECT BeforeImage FROM AuditLog WHERE AuditPK = 1
<CATTYPE>1</CATTYPE>
<CATCODE>UNSPEC</CATCODE>
<CATDESC>AMBIGUOUS SOURCE VALUE</CATDESC>
<CATRANK />
<OBJECTTYPE>-1</OBJECTTYPE>
SELECT BeforeImage FROM AuditLog WHERE AuditPK = 2
<CATCODE>PANTS</CATCODE>
<CATTYPE>1</CATTYPE>

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Please describe the rules how to assign an XML tag name to a specific value.

The way it seems like it is designed: the data are inserted based on the "position" of a column in the create table statement.

What would happen to your audit concept if I'd open the table L_APPCATTYPES in SSMS in design mode and move the column OBJECTTYPE one position up (so it will appear before CATRANK)? I'm not saying that's recommended, but I've seen it just too often....

(As a side note: I purposely picked two columns with identical data type.)

Regarding your original question:

You could join the result of your split string function to sys.tables and sys.columns

SELECT AuditLog.*,s.*, c.name

FROM AuditLog

CROSS APPLY SSC_Test.dbo.DelimitedSplit8K(BeforeImage,'|') s

INNER JOIN sys.tables t ON t.name=AuditLog.Tablename

INNER JOIN sys.columns c ON t.object_id=c.object_id AND c.column_id=itemnumber

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

robbase9 Mr or Mrs. 500 Points: 517 More actions · Answer 2

I'm very new to XML in SQL Server, so i'm not exactly sure how to answer your first question.

As to the second, it would be bad if someone were to change the order of the columns. That's why we're changing it to XML so that we'll have the column names with the correct data.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

Here is one way for PK = 1:

WITH cte(element)

AS (SELECT CONVERT(XML, '<root><i>' + REPLACE(beforeImage, '|', '</i><i>') + '</i></root>') AS element

FROM AuditLog

WHERE AuditPK = 1

)

SELECT element.value('/root[1]/i[1]', 'varchar(100)') AS CATTYPE,

element.value('/root[1]/i[2]', 'varchar(100)') AS CATCODE,

element.value('/root[1]/i[3]', 'varchar(100)') AS CATDESC,

element.value('/root[1]/i[4]', 'varchar(100)') AS CATRANK,

element.value('/root[1]/i[5]', 'varchar(100)') AS OBJECTTYPE

FROM cte

FOR XML PATH('ROOT') ;

You could create one of these for each of your PK values.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

robbase9 Mr or Mrs. 500 Points: 517 More actions · Answer 4

I tried something like that at first, but the column names change. So i can't hardcode the column names in the SELECT FROM cte. I tried creating dynamic sql but couldn't figure it out.