Help with Stored Procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help with Stored Procedure

Author

Topic

Ralphy
Starting Member

3 Posts

Posted - 2012-02-28 : 09:28:39

Hi Experts,

Could some one kindly help me with a Stored Procedure.
I want to generate a XML String as in below format corresponding to each NameID in my table.
I have 5 tables as below

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)
SED_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)

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'">
<Amount amt="200"/>
</Address>
<SED name="SED2" desc = "SED2 Desc"/>
<SED name="SED3" desc = "SED3 Desc">
<SED name="SED4" desc = "SED4 Desc"/>
</SED>
</SED>
</Name>
The SED table can have parent child relationship upto nlevel.
How can we write a StoredProcedure to return a XMLString corresponding to each Name_ID in my table.

Please help.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-02-28 : 11:05:36

can you please post real dml and ddl with sample data? will be easier to help you out

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-28 : 11:25:07

I wanted to repeat what yosiasz suggested, but I had also written some code before I saw his post. So I will post what I wrote anyway. If that does not get you what you want, definitely post sample data.

In general, I would suggest using XML PATH approach. If all the data is flat, you could simply do this - I am showing only a few columns and two joins - you will need to extend it:

SELECT
	tn.Name AS [Name/@name],
	tn.[Desc] AS [Name/@desc],
	ta.Name AS [Name/Address/@name],
	ta.[Desc] AS [Name/Address/@desc]
FROM
	tbl_Name tn
	INNER JOIN tbl_Address ta ON ta.SED_ID = tn.SED_ID
FOR XML PATH('');

If you have nested hierarchy - for example, multiple addresses for the same name, you can nest the XML by nesting the T-SQL query like this:

SELECT
	tn.Name AS [Name/@name],
	tn.[Desc] AS [Name/@desc],
	(
		SELECT
			ta.name AS [@name],
			ta.[Desc] AS [@desc]
		FROM
			tbl_Address ta
		WHERE
			ta.SED_ID = tn.SED_ID
		FOR XML PATH('Address'), TYPE
	)
FROM
	tbl_Name tn
FOR XML PATH('');

Ralphy
Starting Member

3 Posts

Posted - 2012-02-28 : 11:49:33

quote:
Originally posted by Ralphy

Hi Experts,

Could some one kindly help me with a Stored Procedure.
I want to generate a XML String as in below format corresponding to each NameID in my table.
I have 5 tables as below

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)
SED_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)

Required Format
<Name name="Name" desc ="Name1 Desc">
<Address name="Address1" desc="Address1 Desc">
<Amount>"200"</Amt>
</Address>
<SED name="SED1" desc = "SED1 Desc">
<Address name="Address2" desc="Address2 Desc'">
<Amount>"200"</Amt>
</Address>
<SED name="SED2" desc = "SED2 Desc"/>
<SED name="SED3" desc = "SED3 Desc">
<SED name="SED4" desc = "SED4 Desc"/>
</SED>
</SED>
</Name>
The SED table can have parent child relationship upto nlevel.
How can we write a StoredProcedure to return a XMLString containing more than 1 SED structure(n level)corresponding to each Name_ID in my table.

Please help.
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,
)

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)

I have attached the dummy data.
I need the XML String in the above mentined format(Required format)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-28 : 19:13:20

I was not able to run the code you posted. It does not compile because of syntax errors, which I was able to correct. But, there seems to be logical errors as well - for example, the table tbl_SED has five columns, but the insert statements have four columns, and the missing fifth column seems like a critical piece of information.

Nonetheless, just looking at the code, I think, extending the code that I posted earlier would work.

Ralphy
Starting Member

3 Posts

Posted - 2012-02-28 : 20:32:43

quote:
Originally posted by Ralphy

quote:
Originally posted by Ralphy

Hi Experts,

Could some one kindly help me with a Stored Procedure.
I want to generate a XML String as in below format corresponding to each NameID in my table.
I have 5 tables as below

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)
SED_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)

Required Format
<Name name="Name" desc ="Name1 Desc">
<Address name="Address1" desc="Address1 Desc">
<Amount>"200"</Amt>
</Address>
<SED name="SED1" desc = "SED1 Desc">
<Address name="Address2" desc="Address2 Desc'">
<Amount>"200"</Amt>
</Address>
<SED name="SED2" desc = "SED2 Desc"/>
<SED name="SED3" desc = "SED3 Desc">
<SED name="SED4" desc = "SED4 Desc"/>
</SED>
</SED>
</Name>
The SED table can have parent child relationship upto nlevel.
How can we write a StoredProcedure to return a XMLString containing more than 1 SED structure(n level)corresponding to each Name_ID in my table.

Please help.
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)
SELECT '0','SED1','SED1 Desc','', 0 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,
)

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)

I have attached the dummy data.
I need the XML String in the above mentined format(Required format)

Hi sorry for the trouble i have corrected the insert statement for SED table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources