Update/Insert using XML strings as parameters

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update/Insert using XML strings as parameters

Author

Topic

Billpl
Yak Posting Veteran

71 Posts

Posted - 2008-05-20 : 03:36:40

Hi All,

We got this little issue of passing around (updated and inserting) small dataSets (20-500 or so records) from fat clients to a remote server running .Net / SQL 2005.

Instead of trying to fudge the data and make updates in .Net, we just decided it would be a lot less nonsense if we just wrap up the dataSets in an XML string, have .Net just pass it thru as a parameter in a SP and let SQL parse it out using openXML. The data is small and server is low use, so I'm not worried about overhead, but I would like to know the best methods and DO's & Don'ts to parse the XML and make the updates/inserts....and maybe a few good examples. The few examples I've come across are kind of sketchy. Seems it's not a real popular method of handling updates.

Thanks in Advance,
Bill

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-05-20 : 04:58:19

may be this can help

http://weblogs.sqlteam.com/peterl/archive/2008/03/26/Some-XML-search-approaches.aspx

http://www.sql-server-performance.com/articles/dev/xml_data_2005_p2.aspx

Billpl
Yak Posting Veteran

71 Posts

Posted - 2008-05-20 : 13:20:21

thanks,

...to be a little more precise, I'm really asking a stupid question (trying to disguise it)

All I want is the best (right) way to stuff the following XML (as parameter) into the table ItemValues

(long while back we did some stuff in openXML and For XML but that stuff is history, correct?)

--------------------------------------------
CREATE TABLE [ItemValues](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[pro_numb] [int] NULL,
[ItemNumber] [int] NULL,
[pro_ino] [int] NULL,
[pro_icode] [char](12) NULL,
[pro_basis] [char](20) NULL,
[pro_bcost] [money] NULL
) ON [PRIMARY]
-----------------------------------------------
<xdata>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
</xdata>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-20 : 13:43:51

quote:
Originally posted by Billpl

thanks,

...to be a little more precise, I'm really asking a stupid question (trying to disguise it)

All I want is the best (right) way to stuff the following XML (as parameter) into the table ItemValues

(long while back we did some stuff in openXML and For XML but that stuff is history, correct?)

--------------------------------------------
CREATE TABLE [ItemValues](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[pro_numb] [int] NULL,
[ItemNumber] [int] NULL,
[pro_ino] [int] NULL,
[pro_icode] [char](12) NULL,
[pro_basis] [char](20) NULL,
[pro_bcost] [money] NULL
) ON [PRIMARY]
-----------------------------------------------
<xdata>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
</xdata>

Load the xml into a xml variable and use Xquery method to get fields out into table.

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-05-20 : 13:45:11

No openxml is still not history.
I would had gone with this approach

CREATE TABLE [ItemValues](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[SiteID] [int] NULL,
[pro_numb] [int] NULL,
[ItemNumber] [int] NULL,
[pro_ino] [int] NULL,
[pro_icode] [char](12) NULL,
[pro_basis] [char](20) NULL,
[pro_bcost] [money] NULL
)

DECLARE @doc xml
SET @doc = N' <xdata>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>898</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>83</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>95.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>2</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
<md>
<pro_numb>180</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>3</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>
</xdata>
'

DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @doc

insert into [ItemValues](pro_numb,ItemNumber,pro_ino,pro_icode,pro_basis,pro_bcost)

SELECT * FROM
OPENXML(@docHandle, 'xdata/md', 10)
WITH
(pro_numb int,
itemnumb int,pro_ino int,pro_icode int,pro_basis char(20),pro_bcost money
)

EXEC sp_xml_removedocument @docHandle
select * from [ItemValues]

drop table [ItemValues]

Billpl
Yak Posting Veteran

71 Posts

Posted - 2008-05-20 : 18:38:34

Ok, I put things together and below works perfect

but is this the best way?
Does OpenXML have some advantages?

--------------------------
declare @xdata as xml
set @xdata =
'<data>
<md>
<pro_numb>624</pro_numb>
<itemnumb>105</itemnumb>
<pro_ino>1</pro_ino>
<pro_icode/>
<pro_basis>Each</pro_basis>
<pro_bcost>250.000</pro_bcost>
</md>.
....
</data>'

Insert into dbo.itemvalues (pro_numb,itemNumber,pro_ino,pro_icode,pro_basis,pro_bcost)

Select md.rec.value('pro_numb[1]','int'),
md.rec.value('itemnumb[1]','int'),
md.rec.value('pro_ino[1]','int'),
md.rec.value('pro_icode[1]','char(12)'),
md.rec.value('pro_basis[1]','char(20)'),
md.rec.value('pro_bcost[1]','money')
from @xdata.nodes( '/data/md' ) as md(rec)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources