Extracting XML from a a row of SQL table Generically

Question

I have a SQL table that maintains the Auditing of my project and keeps a track of values updated, inserted and deleted in a column of newValue and oldValue in XML format.

AUDIT_ID    TABLE_ID_PK NEW_VALUES  OLD_VALUES  TABLE_NAME  AUDIT_TYPE  COLUMN_00   COLUMN_01   COLUMN_02   COLUMN_03   COLUMN_04   CREATED_BY  AUDIT_ACTION    AUDIT_TIMESTAMP
1   70020   <USERS><USERNAME>asf</USERNAME><LOGINID>saas</LOGINID><PASSWORD>Password</PASSWORD><EMAIL>ooo@hotmail.com</EMAIL><ACTIVE>1</ACTIVE><CELLNO>325235</CELLNO></USERS>      USERS   INSERT  NULL    NULL    NULL    NULL    NULL    1054    Insert Record -- After Insert Trigger.  2019-11-15 19:01:17.260

However,
I need to split the XML So all each value is represented as a row the problem is I need a way to extract XML from the table generically as currently, I have to provide the root and the child nodes of the XML

However the problem is that this code works only for the row with the root of user not for XML date where root and child nodes will be different

 SELECT 
        nN.value('(./USERNAME/text())[1]','Varchar(50)') as USERNAME
     , nN.value('(./LOGINID/text())[1]','Varchar(50)') as LOGINID
        , nN.value('(./PASSWORD/text())[1]','Varchar(50)') as PASSWORD
     , nN.value('(./EMAIL/text())[1]','Varchar(50)') as EMAIL
      , nN.value('(./ACTIVE/text())[1]','Varchar(50)') as ACTIVE
        , nN.value('(./CELLNO/text())[1]','Varchar(50)') as CELLNO
    into #TempTable FROM @xmlNew.nodes('USERS') as a(nN

)

The new Value is

<USERS>
    <USERNAME>asf</USERNAME>
    <LOGINID>saas</LOGINID>
    <PASSWORD>Password</PASSWORD>
    <EMAIL>ooo@hotmail.com</EMAIL>
    <ACTIVE>1</ACTIVE>
    <CELLNO>325235</CELLNO>
</USERS>

The old value is:

<USERS>
    <USERNAME>23f</USERNAME>
    <LOGINID>124saawsws</LOGINID>
    <PASSWORD>Password</PASSWORD>
    <EMAIL>dfsdf@hotmail.com</EMAIL>
    <ACTIVE>1</ACTIVE>
    <CELLNO>2352352350</CELLNO>
</USERS>

I am posting my code below for Guide

declare @counter int ;
--set @counter = 0 ;
select @counter =  max(AUDIT_ID) from SQL_AUDIT_TRAIL
delete from SQL_AUDit_DUMMY

--DROP TABLE IF EXISTS dbo.#TempTable

--DROP TABLE IF EXISTS dbo.#TempTable2
--DROP TABLE IF EXISTS dbo.#temp4
--DROP TABLE IF EXISTS dbo.#temp3

While @counter >= 0
BEGIN

DROP TABLE IF EXISTS dbo.#TempTable

DROP TABLE IF EXISTS dbo.#TempTable2
DROP TABLE IF EXISTS dbo.#temp4
DROP TABLE IF EXISTS dbo.#temp3



DECLARE @xmlNew xml
SET @xmlNew = (select NEW_VALUES from SQL_AUDIT_TRAIL where AUDIT_ID = @counter)--@counter


DECLARE @xmlOld xml
SET @xmlOld = (select OLD_VALUES from SQL_AUDIT_TRAIL where AUDIT_ID = @counter)--@counter


SELECT 
    nN.value('(./USERNAME/text())[1]','Varchar(50)') as USERNAME
 , nN.value('(./LOGINID/text())[1]','Varchar(50)') as LOGINID
    , nN.value('(./PASSWORD/text())[1]','Varchar(50)') as PASSWORD
 , nN.value('(./EMAIL/text())[1]','Varchar(50)') as EMAIL
  , nN.value('(./ACTIVE/text())[1]','Varchar(50)') as ACTIVE
    , nN.value('(./CELLNO/text())[1]','Varchar(50)') as CELLNO
into #TempTable FROM @xmlNew.nodes('USERS') as a(nN)


SELECT 
    nN.value('(./USERNAME/text())[1]','Varchar(50)') as USERNAME
 , nN.value('(./LOGINID/text())[1]','Varchar(50)') as LOGINID
    , nN.value('(./PASSWORD/text())[1]','Varchar(50)') as PASSWORD
 , nN.value('(./EMAIL/text())[1]','Varchar(50)') as EMAIL
  , nN.value('(./ACTIVE/text())[1]','Varchar(50)') as ACTIVE
    , nN.value('(./CELLNO/text())[1]','Varchar(50)') as CELLNO
into #TempTable2 FROM @xmlOld.nodes('USERS') as a(nN)


SELECT
    COLUMNName
    ,NewValue
into #temp4 
FROM #TempTable
UNPIVOT
(
NewValue
FOR COLUMNName IN (USERNAME, LOGINID, PASSWORD, EMAIL, ACTIVE,CELLNO)
) unpvt;


SELECT
    COLUMNName
    ,OldValue
    into #temp3
FROM #TempTable2
UNPIVOT
(
OldValue
FOR COLUMNName IN (USERNAME, LOGINID, PASSWORD, EMAIL, ACTIVE,CELLNO)
) unpvt;


select @counter , t2.COLUMNName,t1.COLUMNName,t1.NewValue,t2.OldValue from #temp4 t1 full join #temp3 t2 on t1.COLUMNName = t2.COLUMNName

insert into SQL_AUDit_DUMMY (Audit_ID , COLUMNName , COLUMNName1 , NewValue , OldValue) 
select @counter , t2.COLUMNName,t1.COLUMNName,t1.NewValue,t2.OldValue from #temp4 t1 full join #temp3 t2 on t1.COLUMNName = t2.COLUMNName

set @counter = @counter - 1
END --End While loop 


select * from SQL_AUDit_DUMMY

Why so you have the square brackets [1] which is giving first row of the table? — jdweng, Nov 22 '19 at 13:21
I do not fully understand what you are trying to do here... If you want to create a XML based Audit system, you might read [this answer (and of course all the other answers there)](https://stackoverflow.com/a/45766198/5089204). This won't be fast and it was more kind of fun, but you will get some ideas... — Shnugo, Nov 22 '19 at 14:22
Do it in two steps 1) Put rows of "Users" into TempTable2. 2) Then query TempTable2 and get value of each column so you do not need the [1] and you will get every row. — jdweng, Nov 22 '19 at 14:56
The problem is that right now my auditing works only Users and but it will have values for all example the units that were added and property that was sold I can hardcode every value that could exist in the system of every as i have done for users Instead of nN.value('(./USERNAME/text())[1] I can write nN.value('(./PropertyName/text())[1] and nodes('Property') but I want to be generic and achieve this in a single query — k21Anaiyalate, Nov 25 '19 at 06:09
@K142073ShahzaibAhmedKhan, the only chance is dynamically created SQL. There is no *magic generic* approach... The answer I've linked shows one approach (which won't be fast...) In general I would use *code geneneration to avoid manual typing and still use dedicated code per table. — Shnugo, Nov 25 '19 at 10:21

Extracting XML from a a row of SQL table Generically

0 Answers0