SQL Server: Output an XML field as tabular data using a stored procedure
- by Pawan
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:
1st record:
<client>
<name>xyz</name>
<ssn>432-54-4231</ssn>
</client>
2nd 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.
