SQLServerCentral / Discuss Content Posted by Jacob Sebastian / Article Discussions / Article Discussions by Author / Saving a Sales Order Part 1 / Latest Posts

RE: Saving a Sales Order Part 1

yazalpizar_ — Wed, 21 Dec 2011 00:30:36 GMT

Hi, very good series of articles, I'm starting from the first one, hope once reach the last one of the serie I can apply all I learned on MSSQL 2008R2 where I'm working on.I found an issue, at least on this first part. The links of the source code does not lead to the correct place, no code is shown.

RE: Saving a Sales Order Part 1

jacob sebastian — Tue, 27 Mar 2007 23:47:00 GMT

Hi,

In part III of this series, I have presented the sample code which handles the insert/update/delete scenario. When a sales order is edited, a few new items may be added, a few items may be modified and/or a few items may be deleted. The sample code in Part III shows an example of how to handle it.

RE: Saving a Sales Order Part 1

sholliday — Tue, 27 Mar 2007 16:16:00 GMT

This is my bread and butter method now. 2000 = OPENXML.

2005 has some different xml functions. Those should be explored.

One of the benefits is that indexes are updated after the entire set of data is inserted or updated.

Another is a "all or nothing" insert or update. You don't orphan any data.

I've added a twist, where I can insert or update, based on the match of a primary key.

Here is an example:

if exists (select * from sysobjects where id = object_id('uspTitleUpdate') and sysstat & 0xf = 4) drop procedure uspTitleUpdate GO

CREATE PROCEDURE dbo.uspTitleUpdate ( @xml_doc TEXT , @numberRowsAffected int output --return )

SET NOCOUNT ON

DECLARE @hdoc INT -- handle to XML doc

DECLARE @errorTracker int -- used to "remember" the @@ERROR

DECLARE @updateRowCount int DECLARE @insertRowCount int

--Create an internal representation of the XML document. EXEC sp_xml_preparedocument @hdoc OUTPUT, @XML_Doc

-- build a table (variable table) to store the xml-based result set DECLARE @titleupdate TABLE ( identityid int IDENTITY (1,1) ,

title_id varchar(6) , title varchar(80) , type varchar(32) , pub_id varchar(32) , price money , advance money , royalty varchar(32) , ytd_sales varchar(32) , notes TEXT , pubdate datetime ,

--used to differeniate between existing (update) and new ones (insert) alreadyExists bit DEFAULT 0 )

--the next call will take the info IN the @hdoc(with is the holder for @xml_doc), and put it IN a variableTable INSERT @titleupdate ( title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdate , alreadyExists ) SELECT title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , dbo.udf_convert_xml_date_to_datetime (pubdate) , --<<UDF to convert xmldate to tsql date 0 FROM -- use the correct XPath .. the second arg ("2" here) distinquishes -- between textnode or an attribute, most times with --.NET typed datasets, its a "2" --This xpath MUST match the syntax of the DataSet OPENXML (@hdoc, '/TitlesDS/Titles', 2) WITH (

title_id varchar(6) , title varchar(80) , type varchar(32) , pub_id varchar(32) , price money , advance money , royalty varchar(32) , ytd_sales varchar(32) , notes TEXT , pubdate varchar(32) , alreadyExists bit)

EXEC sp_xml_removedocument @hdoc

--select * from @titleupdate

--lets differeniate between existing (update) and new ones (insert) Update @titleupdate SET alreadyExists = 1 FROM @titleupdate tu , titles WHERE --this where clause is a little weird, usually you'll must match --primary key (int or global identifiers) ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id)))

SET NOCOUNT OFF

Update titles set title = tu.title , type = tu.type , pub_id = tu.pub_id , price = tu.price , advance = tu.advance , royalty = tu.royalty , ytd_sales = tu.ytd_sales , notes = tu.notes , pubdate = tu.pubdate FROM @titleupdate tu , titles WHERE ltrim(rtrim(upper(titles.title_id))) = ltrim(rtrim(upper(tu.title_id))) AND tu.alreadyExists <> 0

Select @updateRowCount = @@ROWCOUNT

INSERT INTO titles ( title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdate) Select title_id , title , type , pub_id , price , advance , royalty , ytd_sales , notes , pubdateFROM @titleupdate WHERE alreadyExists = 0

Select @insertRowCount = @@ROWCOUNT

select @numberRowsAffected = @insertRowCount + @updateRowCount

--select * from titles

SET NOCOUNT OFF

RE: Saving a Sales Order Part 1

Amara Mustapha — Tue, 27 Mar 2007 13:14:00 GMT

Thanks Paul

I was able to locate all the previous articles and I am going through them. They are very helpful. Thanks to all the experts out there for making DB life a little easier.

Amarado

RE: Saving a Sales Order Part 1

Paul Lach-293939 — Tue, 27 Mar 2007 12:43:00 GMT

Amarado,

I found (by accident) that if you go to the top of this page and click on "Next Topic" it will take you to what appears to be all of the articles in this series. Look quick before they change it!

RE: Saving a Sales Order Part 1

Amara Mustapha — Tue, 27 Mar 2007 11:59:00 GMT

This article is clean and clear. I hope there is a part two that will shed more light on related topics. This would be very helpful for a DBA greener like me. Thanks.

Amarado

RE: Saving a Sales Order Part 1

David.Poole — Tue, 27 Mar 2007 11:48:00 GMT

The bit that concerns me is the BOL entry that warns that this takes 1/8th of the server memory. So a 16GB box is going to lose 2GB RAM just for opening a World document.In SQL2000 I'm pretty sure that the XML parser is MSXML3 where as the most up-to-date version is MSXML6.0.It's a shame because its an elegant solution let down by the underlying technology.

RE: Saving a Sales Order Part 1

jacob sebastian — Tue, 27 Mar 2007 10:42:00 GMT

Hi Paul,

I will definitely collect some performance data and will post it soon.

Jacob

RE: Saving a Sales Order Part 1

Paul Lach-293939 — Tue, 27 Mar 2007 10:34:00 GMT

Your article is very interesting from the viewpoint of presenting a different way to deal with the classic order entry problem. However, it would be useful if you would show some timings for the old way vs. the new way. Specifically:

What is the elapsed time for a single order from a single client? How does the time degrade as the number of users/orders increases?

What is the cost to the SQL Server for each method (CPU, Duration, Reads, Writes)? How does that change as the number of transactions increases?

Again, the technique is interesting, but I think you should show how it compares to the "standard" method from a timing and resources point of view.

RE: Saving a Sales Order Part 1

jacob sebastian — Tue, 27 Mar 2007 08:38:00 GMT

:-)

RE: Saving a Sales Order Part 1

Jeff Gray — Tue, 27 Mar 2007 08:28:00 GMT

Hi jacob,

That's what I get for attempting comprehension before the coffee kicks in.

RE: Saving a Sales Order Part 1

jacob sebastian — Tue, 27 Mar 2007 08:24:00 GMT

As I mentioned earlier, the last part of this 4 part article has an error handling example, where the XML handle is released if an error occurs.

RE: Saving a Sales Order Part 1

noeld — Tue, 27 Mar 2007 08:16:00 GMT

I think the article was clear and very to the point but besides the advantages of using this technique the drawbacks should also be pointed out. I have had memory issues with openxml in the past even though it seems like a nice tool for the job. MSXML parser is an out-of-process memory intense component and you should be really careful on highly transactional environments.

RE: Saving a Sales Order Part 1

Jeff Gray — Tue, 27 Mar 2007 07:53:00 GMT

I don't think the purpose of this particular use of XML is to make it meaningful. It is used as a method to transport data.

For the purposes of this demo, the author takes a clean approach and concisely shows how to accomplish the task. I would object to taking this approach in the real world, however.

It is a small point, but worthy of consideration IMHO: if the insert fails in such a way that the sproc aborts, you leave an open XML document with no handle available. If this happens enough times you will exhaust the "MEMTOLEAVE" area and be forced to restart your SQL server. I would load the XML into a temporary table, destroy the XML document, and then perform the insert, perhaps after validating the data.

This is a good article and it demonstrates a reasonable use of XML in the database.

RE: Saving a Sales Order Part 1

William Melcher — Tue, 27 Mar 2007 07:52:00 GMT

Other than using XML, have you tried generating a flat file to a shared folder that SQL Server can get to. Then use either OPENROWSET or BULK INSERT to load the data into a staging table where you can look up prices, validate data, etc. From there, you can move valid records into the final table and display errors on any other records left in the staging database.

By using a staging table, you can do bulk updates on the records. For example:

update <staging table> set

unitcost = i.unitcost,

...

from <staging table> s

join items i on s.itemID = i.itemID

where s.poID = @someID

RE: Saving a Sales Order Part 1

Anders Pedersen — Tue, 27 Mar 2007 07:16:00 GMT

Why would it matter if you send it all in XML or just the line items if you are not storing it as XML?

RE: Saving a Sales Order Part 1

jacob sebastian — Tue, 27 Mar 2007 03:39:00 GMT

Hi Confucius247,

I agree with you. Actually this article is part of a series, which demonstrates more and more features with each installment. The purpose of the first installment is to present a very simple usage. The second part of this series does use a single XML parameter which contains the order header information as well as item details. It also demonstrates the use of XML attributes.

Thankx

Jacob

RE: Saving a Sales Order Part 1

Confucius247 — Tue, 27 Mar 2007 02:08:00 GMT

I don't understand why you do half of it with passed variables and the other half with XML, for consitency and also to make the XML meaningful on its own it would make sense to pass the ordernumber, date and customer number in with the rest of the XML.

RE: Saving a Sales Order Part 1

jacob sebastian — Mon, 26 Mar 2007 23:46:00 GMT

Hi mike,

I agree with you. There are many cases where you still need to hit the DB many times. But I suppose most of those scenarios can be avoided if we re-design the process a little bit. I have seen this a few times in the past.

Do you have a scenario in your mind where it is absolutely necessary to hit the DB for each item of an entry?

Thankx

Jacob

RE: Saving a Sales Order Part 1

jacob sebastian — Mon, 26 Mar 2007 23:39:00 GMT

Hi Mike,

This article presents a solution that works with SQL Server 2000 specifically. Towards the end of this series, I have an article that presents the same with the XML data type of SQL Server 2005.

Thankx

Jacob

RE: Saving a Sales Order Part 1

Mike Stoodley — Mon, 26 Mar 2007 22:49:00 GMT

All well and good, but a lot of applications will need to actually retreive data from the data base for each line - like codes, costs, discounts etc as each line is used - perhaps that is why a lot of apps hit the db each time a line is required?.

RE: Saving a Sales Order Part 1

Mike C — Mon, 26 Mar 2007 21:55:00 GMT

I notice a lot of people still using OPENXML to shred XML documents. I've only done light testing, but it appears there are definite resource advantages (much less memory), and there might even be some speed advantages, if you use the built-in XML data type and its .nodes() method instead of OPENXML to shred XML documents.

RE: Saving a Sales Order Part 1

Jim Coons — Mon, 26 Mar 2007 21:15:00 GMT

This is a very good article for me, a .net developer of 5 years, who does less database work and more application development than anything else. The sample code is clean, and concise, just enough detail to to be clear and useful. Thanks again -

Saving a Sales Order Part 1

jacob sebastian — Mon, 12 Mar 2007 09:22:00 GMT

Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jSebastian/2908.asp