Solved

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

Posted on 2015-02-16
4
128 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
  • 2
  • 2
4 Comments
 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 500 total points
Comment Utility
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
Comment Utility
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 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach 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.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now