CODEWITHSUNDEEP
sqlsql-serverxml
MissG
MissG

Reputation: 13

How to convert XML tag names and values into column values

I have the below XML stored in a XML datatype column, (called auditinfo) in a SQL Server database. I would like all the tags after root to be listed under a column heading called LocalName and the corresponding values inside the tags to be listed under a heading called LocalValue. 

/** example data in row 1  of the auditinfo column **/
<root>
<Key>50</Key>
<OrderNumber>123</OrderNumber>
<OrderStatus>Ready</OrderStatus>
<CreatedBy>Tom Smith</CreatedBy>
<ReadyDateTime>2015-06-27T10:24:15.743Z</ReadyDateTime>
</root> 


/** example data in row 2  of the auditinfo column **/
<root>
<Key>100</Key>
<Signature>Jill Smith</Signature>
<DeliveryNumber>500</DeliveryNumber>
</root>

Upvotes: 0

Views: 1543

Answers (2)

Abhishek
Abhishek

Reputation: 2490

I hope you are looking for something like this - 

    SELECT T.C.value('fn:local-name(.)', 'nvarchar(50)') AS LocalName,
       T.C.value('.','VARCHAR(MAX)') AS LocalValue  
FROM YourXmlTable 
CROSS APPLY [auditinfo].nodes('/root/*') AS T(C)

Upvotes: 2

Shadow Walker
Shadow Walker

Reputation: 206

this can be saved as 

keys  ordernumber orderstatus createdby    readydatetime
50     123         ready      tom simth    2015-06-27T10:24:15.743Z

if u want like this i have solution

@XMLVariable nvarchar(max)-- parameter from front end


Declare @XMLorderList as xml
set @XMLOrderList=@XMLVariable 


IF Object_id('tempdb..#tempLocaltable') Is Not Null     
            Drop table #tempLocaltable

    CREATE TABLE #tempLocaltable
     (
        key nvarchar(100),
        OrderNumber nvarchar(200),
        OrderStatus nvarchar(200),
        Createdby nvarchar(200),
        ReadyDateTime nvarchar(200),        
        )   

INSERT INTO #tempLocaltable
    SELECT * FROM 
    ( SELECT 
        x.rec.value('key[1]', 'nvarchar(100)') as key,
        x.rec.value('Ordernumber[1]', 'nvarchar(200)') as ordernumber,
        x.rec.value('Orderstatus[1]','nvarchar(200)') as orderstatus,
        x.rec.value('createdby[1]','nvarchar(200)') as createdby,
        x.rec.value('ReadyDateTime[1]','nvarchar(200)') as ReadyDateTime


    FROM @XMLOrderList.nodes('/root') x(rec) ) t




 insert into urtable (key,ordernumber,orderstatus,createdby,readydate)
        (select key,ordernumber, orderstatus,createdby,readydate from          
   #tempLocaltable)

Upvotes: 0

Related Questions