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)
asked
Insert data from T_A to T_B in follwing T_B format, without using while loop or cursors
You can do a
INSERT dbo.T_B (Col1, Col2) SELECT Col1, Col2 from dbo.T_A
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.
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
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
17 People are following this question.
