Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

Sales Order Workshop Part IV Expand / Collapse
Author
Message
Posted Wednesday, October 1, 2008 4:14 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Wednesday, October 1, 2008 5:35 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, February 2, 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
Posted Thursday, June 3, 2010 4:40 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:35 AM
Points: 2,126, Visits: 1,473
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
Posted Thursday, June 3, 2010 5:08 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Thursday, June 3, 2010 5:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 2:35 AM
Points: 2,126, Visits: 1,473
Brilliant!! Thanks very much

I find this XML stuff a real struggle.

Lempster
Post #932002
Posted Thursday, June 3, 2010 5:33 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, December 24, 2013 4:42 AM
Points: 460, Visits: 2,523
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
Posted Friday, December 23, 2011 2:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 7:51 AM
Points: 144, Visits: 499
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 »

Add to briefcase «««123

Permissions Expand / Collapse