question

UsmanRasheed avatar image
UsmanRasheed asked

Insert data from T_A to T_B in follwing T_B format, without using while loop or cursors

I have two tables, T_A (main source) And T_B where i want to insert data from t_A, but i don't want to use while loop or cursors.
Following File is for T_A & T_B, (I already inserted data in T_B, but in reality its empty)


T_A & T_B.sql

xmlinsertdatasets
t-a-t-b.sql (60.4 KiB)
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

JohnM avatar image
JohnM answered

You can do a

INSERT dbo.T_B (Col1, Col2) 
SELECT Col1, Col2 from dbo.T_A


2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi, JohnM , Thanks for reply ,this will insert only col1 and col2 into T_B, thats not what i want, i have xml links in T_A, which i want to shred in specific format along with T_A other columns.

Like shown in following image.

0 Likes 0 ·
t-at-b.png (68.4 KiB)

I am using following query which is working almost fine, but columns names are not as desired.

INSERT INTO dbo.T_B
(ID,Name,Version,Checkup,Columns_Names,Data)
SELECT
xt.Id
,xt.Name
,xt.VERSION
,xt.checkup
,m.value('local-name(.)','varchar(150)')
,ISNULL(x.m.value('text()[1]','varchar(max)'),'')
From dbo.T_A xt
Cross apply xt.xmlpath.nodes('//*') x(m)
WHERE xt.checkup =0

0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered

You are nearly there with your query, you'd just need to build up the full path. You can achieve that by using a recursive CTE to walk through the XML structure.

Or do what I always do, and use the useful XMLTable function by Jacob Sebastian (https://gist.github.com/jacobvettickal/601c197d716d0aeb40c1c177ca1503d8)

Add this function to your database and then query it using

select
xt.ID
,xt.Name
,xt.VERSION
,xt.checkup
,x.FullPath
,x.Value
from dbo.T_A xt
cross apply dbo.XMLTable(xt.XMLPath) x
where xt.checkup =0

You might need to manipulate the output a little if you want the leading forward slash, and/or remove @ symbols from attributes, but it should get you further


10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.