SQL Server: Output an XML field as tabular data using a stored procedure

Question

I am using a table with an XML data field to store the audit trails of all other tables in the database.

That means the same XML field has various XML information. For example my table has two records with XML data like this:

1^st record:

<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>

2^nd record:

<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>

These are the two sample formats and just two records. The table actually has many more XML formats in the same field and many records in each format.

Now my problem is that upon query I need these XML formats to be converted into tabular result sets.

What are the options for me? It would be a regular task to query this table and generate reports from it. I want to create a stored procedure to which I can pass that I need to query "<emp>" or "<client>", then my stored procedure should return tabular data.

Answer 1

Neither xyz432-54-4231 nor abc5000 is valid XML.

You can try to select only one particular format with a like statement, f.e.:

select * 
from YourTable 
where YourColumn like '[a-z][a-z][a-z][0-9][0-9][0-9][0-9]'

This would match 3 letters followed by 4 numbers.

A better option is probably to add an extra column to the table, where you save the type of the logging. Then you can use that column to select all "emp" or "client" rows.

Answer 2

An option would be to create a series of views that present the aduit table, per type in the relations that you're execpting for example

select
    c.value('name','nvarchar(50)') as name,
    c.value('ssn', 'nvarchar(20)') as ssn
from yourtable
cross apply yourxmlcolumn.nodes('/client') as t(c)

you could then follow the same pattern for the emp

you could also create a view (or computed column) to identify each xml type like this:

select yourxmlcolumn.value('local-name(/*[1])', 'varchar(100)') as objectType
from yourtable

Answer 3

Use open xml method

DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

SELECT * into #test
FROM OPENXML (@idoc, 'xmlfilepath',2)
WITH (Name varchar(50),ssn varchar(20)
)

EXEC sp_xml_removedocument @idoc

after you get the data in the #test and you can manipulate this.

you may be put the diff data in diff xml file.

Answer 4

does this help?

INSERT INTO @t (data) SELECT '
<client>
  <name>xyz</name>
  <ssn>432-54-4231</ssn>
</client>'

INSERT INTO @t (data) SELECT '
<emp>
  <name>abc</name>
  <sal>5000</sal>
</emp>'
DECLARE @el VARCHAR(20)

SELECT @el = 'client'

SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
client               xyz432-54-4231
*/

SELECT @el = 'emp'
SELECT
    x.value('local-name(.)', 'VARCHAR(20)') AS ColumnName,
    x.value('.','VARCHAR(20)') AS ColumnValue
FROM @t
CROSS APPLY data.nodes('/*[local-name(.)=sql:variable("@el")]') a (x)
/*
ColumnName           ColumnValue
-------------------- --------------------
emp                  abc5000
*/