April 30, 2014 at 5:26 am
Objective: To insert into Mod_Primary,Mod_Secondary columns
I should get all values of ModuleList element from XML and join it with Module table on XML<Module>=Module Table.ModuleID and get Module Desc seperated by commas grouped by Module_Category
Mod_Primary='ABC,XYZ'
Mod_secondary='PQR'
CREATE TABLE Table1(
[ID] int,
[Version] int,
[Start_Date] Date,
[End_Date] Date,
[XML_Uncompressed] [xml] NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Create Table Table2(
[No] int,
[Version] int,
[Start_date] Date,
[End_Date] Date,
[Age] int,
[Name] varchar(50),
[City] varchar(50),
[Postcode] varchar(50),
[Mod_Primary] varchar(50),
[Mod_Secondary] Varchar(50)
)
Create Table Module(
MOduleID varchar(30),
MOdule_Cat varchar(30),
Module_Desc Varchar(30))
Insert into module values('P01','Primary','ABC')
Insert into module values('P02','Primary','XYZ')
Insert into module values('C01','Secondary','PQR')
Insert into Table1
(Id,
version,
Start_Date,
End_Date,
XMl_UnCompressed)
values
(
3
,2
,'2005-04-12'
,'2012-04-12'
,'<qsds:Details xmlns:qsds="http://www.abc.com/Details">
<Modulelist>
<Module>P01</Module>
<Module>P02</Module>
<Module>C01</Module>
</Modulelist>
<Age>35</Age>
<Name>MR</Name>
<City>BGLRE</City>
<Postcode>123</Postcode>
</qsds:Details>'
)
;with XMLNAMESPACES (
'http://www.abc.com/Details' as qsds)
INSERT INTO [dbo].[Table2]
([No]
,[Version]
,[Start_date]
,[End_Date]
,[Age]
,[Name]
,[City]
,[Postcode]
--,[Mod_Primary]
--,[Mod_Secondary]
)
Select
ID
,Version
,Start_Date
,End_Date
,XML_Uncompressed.value('(qsds:Details/Age)[1]','varchar(50)')
,XML_Uncompressed.value('(qsds:Details/Name)[1]','varchar(50)')
,XML_Uncompressed.value('(qsds:Details/City)[1]','varchar(50)')
,XML_Uncompressed.value('(qsds:Details/Postcode)[1]','varchar(50)')
From Table1
April 30, 2014 at 7:03 am
This should get you started
😎
;with XMLNAMESPACES (
'http://www.abc.com/Details' as qsds)
Select
ID
,Version
,Start_Date
,End_Date
,DE.TAILS.value('Age[1]','varchar(50)') AS Age
,MO.DULE.value('.[1]','varchar(50)') AS MModule
,MD.*
FROM Table1 T1
OUTER APPLY T1.XML_Uncompressed.nodes('qsds:Details') AS DE(TAILS)
OUTER APPLY DE.TAILS.nodes('Modulelist/Module') AS MO(DULE)
INNER JOIN dbo.Module MD ON MD.MOduleID = MO.DULE.value('.[1]','varchar(50)')
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply