Output XML data with sub datasets from SQL to .NET web service

Hello,

I have two data tables which are linked together which I need to return in a web service but several of the columns I need to display as a sub dataset (may be the incorrect term) in XML to be returned but I am unsure the best way to do this.

Table 1 - tbl_items
Item_Code | Item_Description | Part_1 | Part_2 | Part_3
R0000001 | Red Colour Item  | A0001 | B0001 | C0001
Y0000002 | Yellow Colour Item  | C0001 | D0001 | E0001
C0000003 | Connector Item | E0001

Table 2 - tbl_parts
Part_Code | Part_Description | Part_Cost
A0001 | Red Part  | 1.00
B0001 | Small Part  | 1.50
C0001 | Big Part  | 2.50
D0001 | XL Part  | 3.50
E0001 | Connector  | 0.50


Obviously the easy way to do this would be to just have a select statement that returned the list but not all items have all the parts columns filled and there is a need to send it back in the following format.

<items>
  <item>
    <item_code>R0000001</item_code>
    <item_description>Red Colour Item</item_description>
    <item_cost>5.00</item_cost>
    <parts>
      <part>
        <part_code>A0001</part_code>
        <part_description>Red Part</part_description>
        <part_cost>1.00</part_cost>
      </part>
      <part>
        <part_code>A0001</part_code>
        <part_description>Red Part</part_description>
        <part_cost>1.00</part_cost>
      </part>
      <part>
        <part_code>B0001</part_code>
        <part_description>Small Part</part_description>
        <part_cost>1.50</part_cost>
      </part>
      <part>
        <part_code>C0001</part_code>
        <part_description>Big Part</part_description>
        <part_cost>2.50</part_cost>
      </part>
    </parts>
  </item>
  <item>
    <item_code>C0000003</item_code>
    <item_description>Connector Item</item_description>
    <item_cost>0.50</item_cost>
    <parts>
      <part>
        <part_code>E0001</part_code>
        <part_description>Connector</part_description>
        <part_cost>0.50</part_cost>
      </part>
    </parts>
  </item>
</items>

Open in new window


I have been looking at T-SQL FOR XML to return to the web service as XML which I think would be the easiest way to handle this unless there is a way of using C# to format the dataset in this way but I would suspect it would require a 2nd call so doing it all in one request makes more sense I feel.

I did manage to get it to work for one part using:

SELECT item.item_code, item.item_description, part.part_code, part.part_description, part.part_cost
FROM tbl_items AS item
	INNER JOIN tbl_parts AS part
	ON item.part_code = parts.part_code
	FOR XML AUTO, ROOT ('items'), ELEMENTS;

Open in new window


The issue I am having is I can not for the life of me figure out how to have an array of parts returned for each individual part column as the XML above shows.

I am sure it is really simple but I am well and truly stuck.

Thanks,

Lee
Lee RedheadManaging DirectorAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ste5anConnect With a Mentor Senior DeveloperCommented:
D'oh? It's hard when we need to guess your table structure..

DECLARE @tbl_items TABLE 
(
	Item_Code NVARCHAR(255),
	Item_Description NVARCHAR(255),
	Part_1 NVARCHAR(255), 
	Part_2 NVARCHAR(255), 
	Part_3 NVARCHAR(255),
	Q1 INT, Q2 INT, Q3 INT
);

INSERT INTO @tbl_items
VALUES	( 'R0000001', 'Red Colour Item', 'A0001', 'B0001', 'C0001', 3, 2, 1 ),
		( 'Y0000002', 'Yellow Colour Item', 'C0001', 'D0001', 'E0001', 3, 2, 1 ),
		( 'C0000003', 'Connector Item', 'E0001', NULL, NULL, 1, NULL, NULL);

DECLARE @tbl_parts TABLE
(
	Part_Code NVARCHAR(255),
	Part_Description NVARCHAR(255),
	Part_Cost MONEY
);
 
INSERT INTO @tbl_parts
VALUES	( 'A0001', 'Red Part', 1.00 ),
		( 'B0001', 'Small Part', 1.50 ),
		( 'C0001', 'Big Part', 2.50 ),
		( 'D0001', 'XL Part', 3.50 ),
		( 'E0001', 'Connector', 0.50 );

WITH Normalized AS (
	SELECT	I.Item_Code,
			I.Item_Description,
			Part_1 AS Part_Code,
			Q1 AS Part_Quantity
	FROM	@tbl_items I
	WHERE	NOT I.Part_1 IS NULL
	UNION 
	SELECT	I.Item_Code,
			I.Item_Description,
			Part_2 AS Part_Code,
			Q2 AS Part_Quantity
	FROM	@tbl_items I
	WHERE	NOT I.Part_2 IS NULL
	UNION 
	SELECT	I.Item_Code,
			I.Item_Description,
			Part_3 AS Part_Code,
			Q3 AS Part_Quantity
	FROM	@tbl_items I
	WHERE	NOT I.Part_3 IS NULL
		)
	SELECT	O.Item_Code,
			O.Item_Description,
			(
				SELECT	P.Part_Code,
						P.Part_Cost,
						P.Part_Description,
						I.Part_Quantity
				FROM	Normalized I
					INNER JOIN @tbl_parts P ON I.Part_Code = P.Part_Code
				WHERE	I.Item_Code = O.Item_Code
				FOR XML PATH('part'), ROOT('parts'), TYPE
			)
	FROM	Normalized O
	GROUP BY O.Item_Code,
		O.Item_Description
	FOR XMl PATH('item'), ROOT('items');

Open in new window


But as I already wrote, it looks like a pivot. So repair that first or create your own view or stored procedure to retrieve your data.
0
 
ste5anConnect With a Mentor Senior DeveloperCommented:
You know that your XML is bloated, cause you have redundant information - the parts - in it?

The problem is your query. It's not normalized. It looks like a pivot. So normalize it. Then it's simple. E.g.

DECLARE @tbl_items TABLE 
(
	Item_Code NVARCHAR(255),
	Item_Description NVARCHAR(255),
	Part_1 NVARCHAR(255), 
	Part_2 NVARCHAR(255), 
	Part_3 NVARCHAR(255)
);

INSERT INTO @tbl_items
VALUES	( 'R0000001', 'Red Colour Item', 'A0001', 'B0001', 'C0001' ),
		( 'Y0000002', 'Yellow Colour Item', 'C0001', 'D0001', 'E0001' ),
		( 'C0000003', 'Connector Item', 'E0001', NULL, NULL );

DECLARE @tbl_parts TABLE
(
	Part_Code NVARCHAR(255),
	Part_Description NVARCHAR(255),
	Part_Cost MONEY
);
 
INSERT INTO @tbl_parts
VALUES	( 'A0001', 'Red Part', 1.00 ),
		( 'B0001', 'Small Part', 1.50 ),
		( 'C0001', 'Big Part', 2.50 ),
		( 'D0001', 'XL Part', 3.50 ),
		( 'E0001', 'Connector', 0.50 );

WITH Normalized AS (
	SELECT	*
	FROM	@tbl_items I
	UNPIVOT ( Part_Code FOR Part_No IN ( Part_1, Part_2, Part_3) ) U
	)
	SELECT	O.Item_Code,
			O.Item_Description,
			(
				SELECT	P.Part_Code,
						P.Part_Cost,
						P.Part_Description
				FROM	Normalized I
					INNER JOIN @tbl_parts P ON I.Part_Code = P.Part_Code
				WHERE	I.Item_Code = O.Item_Code
				FOR XML PATH('part'), ROOT('parts'), TYPE
			)
	FROM	Normalized O
	GROUP BY O.Item_Code,
		O.Item_Description
	FOR XMl PATH('item'), ROOT('items');

Open in new window

0
 
Lee RedheadManaging DirectorAuthor Commented:
That works perfectly and returns exactly what I am looking for. One thing I did forget in the original example was a quantity field in the original table.

I am getting my head around how it is all working but can't work out how to get the quantity in.

Would I need to add the a field Part_Qty to the @tbl_parts table when add something like?

UNPIVOT ( Part_Qty FOR Part_Q IN ( Part_1_Qty, Part_2_Qty, Part_3_Qty) ) UQ

Open in new window


Obviously each part line in the main table would have a quantity assigned to it for the number of each part required.
0
 
Lee RedheadManaging DirectorAuthor Commented:
Yes that was my mistake, it is because there is a lot of extra fields in the actual tables I am working with so I trimmed out some of the fields and just simplified it so I could get my head around the solution as it is the part list that I was struggling with.

That makes sense though the solution so I can adapt that to suit what I am looking at.

Thank you so much for the help, it is a lot further into SQL that I have gone before so still getting my head around it all.
0
All Courses

From novice to tech pro — start learning today.