Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sales Order Workshop Part IV


Sales Order Workshop Part IV

Author
Message
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
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.

.
tymberwyld
tymberwyld
SSC Veteran
SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)SSC Veteran (284 reputation)

Group: General Forum Members
Points: 284 Visits: 274
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



Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
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
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
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
*/



.
Lempster
Lempster
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2683 Visits: 1657
Brilliant!! Thanks very much :-)

I find this XML stuff a real struggle.

Lempster
jacob sebastian
jacob sebastian
SSC-Addicted
SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)SSC-Addicted (472 reputation)

Group: General Forum Members
Points: 472 Visits: 2523
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

.
yazalpizar_
yazalpizar_
SSC-Enthusiastic
SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)SSC-Enthusiastic (152 reputation)

Group: General Forum Members
Points: 152 Visits: 626
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search