Help in writing stored procedure
-
HI SQL Experts,
I faced a doubt with a Stored Procedure Implementation
That is i have got 5 tables in an SQL Server
With huge data.
Tables
1) Name
2) Address
3) Amount
4) SED
5) Elements
1) tbl_Name
Name_ID (PKEY)INT
Name VARCHAR
Desc VARCHAR
SED_ID INT (FKEY)
2) tbl_Address
Address_ID (PKEY)INT
Name VARCHAR
Desc VARCHAR
Name_ID INT (FKEY)
3) tbl_Amount
Amount_ID (PKEY)INT
Amount VARCHAR
Desc VARCHAR
Address_ID INT (FKEY)
4) tbl_SED
SED_ParentID (PKEY)INT
Amount VARCHAR
Desc VARCHAR
SED_ChildID INT
Name_ID INT (FKEY)
Address_ID INT (FKEY)
5) tbl_Elements
ElementID (PKEY)INT
XmlContent NTEXT
Name_ID INT (FKEY)
I want to iterate these tables staring from Name and generate an xml string of these linked tables in the below mentioned format.
Required Format
<Name name="Name" desc ="Name1 Desc">
<Address name="Address1" desc="Address1 Desc">
<Amount amt="100" desc="Amount1 Desc"/>
</Address>
<SED name="SED1" desc = "SED1 Desc">
<Address name="Address2" desc="Address2 Desc'"> ->SED having Address inside it
<Amount amt="200"/>
</Address>
<SED name="SED2" desc = "SED2 Desc"/>
<SED name="SED3" desc = "SED3 Desc"/>
<SED name="SED4" desc = "SED4 Desc"/>
</SED>
</Name>
Insert it into another table known as tbl_Elements containing a field XMLContent (ntext) and a Name_ID (int) in a format as
XMLContent -> must contain the below mentioned format data from all the above mentiond table
The table SED contain SED_Parent and Child details.Its based on Parent Child flow
Could some one help me with a implementation of the SP.
Dummy Data
INSERT INTO #tbl_Name
(Name_ID , Name, Desc, SED_ID)
SELECT '0','Name1','Name1 Desc','0' UNION ALL
SELECT '1','Name2','Name2 Desc','1' UNION ALL
SELECT '2','Name3','Name3 Desc','2' UNION ALL
SELECT '3','Name4','Name4 Desc','3' UNION ALL
INSERT INTO #tbl_Address
(Address_ID , Name, Desc, Name_ID )
SELECT '0','Address1','Address1 Desc','0' UNION ALL
SELECT '1','Address2','Address2 Desc','1' UNION ALL
SELECT '2','Address3','Address3 Desc','2' UNION ALL
SELECT '3','Address4','Address4 Desc','3' UNION ALL
INSERT INTO #tbl_Amount
(Amount_ID , Amount, Desc, Address_ID )
SELECT '0','100','Amount1 Desc','0' UNION ALL
SELECT '1','200','Amount2 Desc','1' UNION ALL
SELECT '2','300','Amount3 Desc','2' UNION ALL
SELECT '3','400','Amount4 Desc','3' UNION ALL
INSERT INTO #tbl_SED
(SED_ID , SEDName, Desc, SED_ParentID,Name_ID,Address_ID)
SELECT '0','SED1','SED1 Desc','',0,1 UNION ALL
SELECT '1','SED2','SED2 Desc','0','','' UNION ALL
SELECT '2','SED3','SED3 Desc','1','','' UNION ALL
SELECT '3','SED4','SED4 Desc','1','','' UNION ALL
-===== Create the test table with
CREATE TABLE #tbl_Name
(
Name_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR,
Desc VARCHAR,
SED_ID INT NOT NULL ,
)
CREATE TABLE #tbl_Address
(
Address_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR,
Desc VARCHAR,
Name_ID INT NOT NULL ,
)
CREATE TABLE #tbl_Amount
(
Amount_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Amount VARCHAR,
Desc VARCHAR,
Address_ID INT NOT NULL ,
)
CREATE TABLE #tbl_SED
(
SED_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Name VARCHAR,
Desc VARCHAR,
SED_ParentID INT ,
Name_ID INT NOT NULL,
Address_ID INT ,
)
CREATE TABLE #tbl_Elements
(
Elements_ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
XMLContent NTEXT NOT NULL,
Name_ID INT NOT NULL ,
)
alter table tbl_Name
add constraint tbl_Name_SED_ID_FK FOREIGN KEY ( SED_ID ) references tbl_SED (SED_ID)
alter table tbl_Address
add constraint tbl_Address_Name_ID_FK FOREIGN KEY ( Name_ID ) references tbl_Name (Name_ID)
alter table tbl_Amount
add constraint tbl_Amount_Address_ID_FK FOREIGN KEY ( Address_ID ) references tbl_Address (Address_ID)
alter table tbl_SED
add constraint tbl_SED_Name_ID_FK FOREIGN KEY ( Name_ID ) references tbl_Name (Name_ID)
alter table tbl_Elements
add constraint tbl_Elements_Name_ID_FK FOREIGN KEY ( Name_ID ) references tbl_Name (Name_ID)
-
Please provide table DDL and sample data in a ready to use format as described in the first link in my signature as well as your expceted output based on those sample data. This will help us to test our solutions.
-
Hi,
I have added the sample data.
Regards
Richu
-
a simple data,how it work?
-
Hi Richu,
I'm not sure if this is what you're looking for (especially in terms of the SED nodes), but here's something to start with:
SELECT'@name' = n.Name,
'@desc' = n.Descr,
'Address/@name' = ad.Name,
'Address/@desc' = ad.Descr,
'Address' =(
SELECT '@amt' = am.Amount,
'@desc' = am.Descr
FROM #tbl_Amount am
WHERE am.Address_ID=ad.Address_ID
FOR XML PATH('Amount'), TYPE
),
(
SELECT
s.Name,
s.Descr,
(
SELECT
'@name' = ad2.Name,
'@desc' = ad2.Descr
FROM #tbl_Address ad2
WHERE s.Address_ID=ad2.Address_ID
FOR XML PATH('Address'), TYPE
),
(
SELECT
'@amt' = am.Amount,
'@desc' = am.Descr
FROM #tbl_Address ad2
INNER JOIN #tbl_Amount am
ON ad2.Address_ID=am.Address_ID
WHERE s.Address_ID=ad2.Address_ID
FOR XML PATH('Amount'), TYPE
)
FROM #tbl_SED s
WHERE n.Name_ID=s.Name_ID
FOR XML RAW('SED'), TYPE
)
FROM #tbl_Name n
INNER JOIN #tbl_Address ad
ON n.Name_ID=ad.Name_ID
FOR XML PATH('Name')
As a side note: I decided to use column names that are not reserved keywords in SQL (e.g. DESC...)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply