SQL Clone
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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 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
SSChasing Mays
SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)SSChasing Mays (602 reputation)

Group: General Forum Members
Points: 602 Visits: 275
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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3189 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
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 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
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

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

I find this XML stuff a real struggle.

Lempster
jacob sebastian
jacob sebastian
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1008 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 (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)SSC-Enthusiastic (174 reputation)

Group: General Forum Members
Points: 174 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