Solved

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

Posted on 2015-02-16
4
132 Views
Last Modified: 2015-02-18
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
0
Comment
Question by:Lee Redhead
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 33

Assisted Solution

by:ste5an
ste5an earned 500 total points
ID: 40613911
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
 

Author Comment

by:Lee Redhead
ID: 40614389
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
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40614592
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
 

Author Closing Comment

by:Lee Redhead
ID: 40616123
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question