Sales Order Workshop Part IV

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jacob Sebastian » Sales Order Workshop Part IV

27 posts, Page 3 of 3

«««1 2 3

Sales Order Workshop Part IV

Rate Topic

Display Mode

Topic Options

Author

Message

jacob sebastian

Posted Wednesday, October 01, 2008 4:14 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

XML data is much more descriptive than CSV. XML is designed with a lot of other goals too. I agree with you that in this case a CSV approach may give you better performance. When transfering large volumns of data, XML may not be the best choice.

.

Post #578938

tymberwyld

Posted Wednesday, October 01, 2008 5:35 AM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, February 02, 2013 8:21 AM
Points: 283, Visits: 268

The only thing I might be able to add to it is to see if using the new "APPLY" operator in your join to your Table function will speed things up even more. I have had some cases where this worked much better, again, it all depends.

Your Xml parsing might be sped up by making a Schema for it and then applying that Schema to the incoming Xml. I've even had Procs that could accept multiple versions and different Schemas.

Ex.
CREATE PROC ParseSomething
(
@Data Xml
)
AS
Declare @Data_v1 Xml(MySchema_v1)

TRY
Set @Data_v1 = @Data
-- At this point, if the Xml does not conform to the Schema,
-- there will be an error or the @Data_v1 will be Null
-- TODO: Do your parsing off the @Data_v1 NOT your original @Data
-- this way the Xml parser has to do less thinking now that there is a valid schema
END TRY
CATCH
SELECT
ERROR_NUMBER()
END CATCH

Post #578975

Lempster

Posted Thursday, June 03, 2010 4:40 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 1,448, Visits: 755

Jacob,

Thanks for the article - very helpful. How would you extend this so that you could extract header and item data for more than one sales order? For example, say that your source XML was

<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
<lineItems>
<item itemNumber="A001" qty="10" rate="10.5" />
<item itemNumber="A002" qty="20" rate="11" />
<item itemNumber="A003" qty="30" rate="13" />
</lineItems>
</salesOrder>
<salesOrder orderNumber="100002" customerNumber="JAC002" orderDate="01-01-2008">
<lineItems>
<item itemNumber="A004" qty="60" rate="15.5" />
<item itemNumber="A005" qty="70" rate="21" />
<item itemNumber="A006" qty="80" rate="33" />
</lineItems>
</salesOrder>

Using this code...

SELECT
y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
x.item.value('@qty[1]','int') AS Qty,
x.item.value('@rate[1]','float') AS Rate
FROM @OrderInfo.nodes('//item') AS x(item)
CROSS APPLY @OrderInfo.nodes('//salesOrder') AS y(header)
ORDER BY OrderNumber

... I get a listing of both order numbers but with ALL the ItemNumbers rather than just the ItemNumbers that belong to each OrderNumber

OrderNumber	ItemNumber	Qty	Rate
100001	             A001	  10	10.5
100001	             A002	  20	11
100001	             A003	  30	13
100001	             A004	  60	15.5
100001	             A005	  70	21
100001	             A006	  80	33
100002	             A001	  10	10.5
100002	             A002	  20	11
100002	             A003	  30	13
100002	             A004	  60	15.5
100002	             A005	  70	21
100002	             A006	  80	33

The result should be just 6 rows, 3 for each order number.

Lempster

Post #931981

jacob sebastian

Posted Thursday, June 03, 2010 5:08 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

How about this?

DECLARE @x XML
SELECT @x = '
<salesOrder orderNumber="100001" customerNumber="JAC001" orderDate="01-01-2007">
	<lineItems>
		<item itemNumber="A001" qty="10" rate="10.5" />
		<item itemNumber="A002" qty="20" rate="11" />
		<item itemNumber="A003" qty="30" rate="13" />
	</lineItems>
</salesOrder>
<salesOrder orderNumber="100002" customerNumber="JAC002" orderDate="01-01-2008">
	<lineItems>
		<item itemNumber="A004" qty="60" rate="15.5" />
		<item itemNumber="A005" qty="70" rate="21" />
		<item itemNumber="A006" qty="80" rate="33" />
	</lineItems>
</salesOrder>'

SELECT
	y.header.value('@orderNumber[1]', 'varchar(20)') as OrderNumber,
	x.item.value('@itemNumber[1]','varchar(10)') AS ItemNumber,
	x.item.value('@qty[1]','int') AS Qty,
	x.item.value('@rate[1]','float') AS Rate
FROM @x.nodes('/salesOrder') AS y(header)
CROSS APPLY header.nodes('lineItems/item') AS x(item)
ORDER BY OrderNumber

/*
OrderNumber          ItemNumber Qty         Rate
-------------------- ---------- ----------- ----------------------
100001               A001       10          10.5
100001               A002       20          11
100001               A003       30          13
100002               A004       60          15.5
100002               A005       70          21
100002               A006       80          33
*/

Post #931993

Lempster

Posted Thursday, June 03, 2010 5:22 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 7:39 AM
Points: 1,448, Visits: 755

Brilliant!! Thanks very much Smile

I find this XML stuff a real struggle.

Lempster

Post #932002

jacob sebastian

Posted Thursday, June 03, 2010 5:33 AM

SSC-Addicted

Group: General Forum Members
Last Login: Saturday, May 11, 2013 8:17 AM
Points: 460, Visits: 2,521

Glad to know it helped. You are right, most people find it bit difficult when they start working with XML..but that will go away soon.

May be the XQuery workshops can help: http://beyondrelational.com/blogs/jacob/archive/2008/06/26/xquery-labs-a-collection-of-xquery-sample-scripts.aspx

.

Post #932012

yazalpizar_

Posted Friday, December 23, 2011 2:01 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 23, 2013 8:49 AM
Points: 139, Visits: 291

Whooaa!! great improvement using XQuery. Will follow the XQuery workbench proposed by you on beyondrelational.com website to get a better understanding on how it works. I get confused using the .nodes, header, etc.

Post #1226123

« Prev Topic | Next Topic »

27 posts, Page 3 of 3

«««1 2 3

Permissions