Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XML Query Expand / Collapse
Author
Message
Posted Wednesday, April 30, 2014 5:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, July 25, 2014 4:37 AM
Points: 36, Visits: 122
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

Post #1566320
Posted Wednesday, April 30, 2014 7:03 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 11:14 AM
Points: 1,587, Visits: 4,339
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)')

Post #1566365
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse