OPENXML - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

OPENXML

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-10-21 : 07:55:03

Majid writes "I created two tables (Majid,MajidDenomination). Then I wrote two procedure to insert and update the tables using xml.
I don't have any problems to insert into the table using OPENXML.
But when I use the update, the first table gets updated and second table (majidDenomination) doesn't get updated and I don't get any error.
Here is the update procedure.

SET QUOTED_IDENTIFIER OFF
DECLARE @xmldoc varchar(1000),@h int
set @xmldoc ='
<root>
<Majid CurrencyNumber="1" CurrencyName="XXX" Symbol="C">
<Denomination Label="$100" CurrencyNumber="1" Amount="900" />
<Denomination Label="$50" CurrencyNumber="1" Amount="900" />
</Majid>
<Majid CurrencyNumber="2" CurrencyName="kkk" Symbol="K">
<Denomination Label="$20" CurrencyNumber="2" Amount="800" />
<Denomination Label="$10" CurrencyNumber="2" Amount="800" />
</Majid>
</root>'
EXEC sp_xml_preparedocument @h OUTPUT, @xmldoc

UPDATE MajidDenomination
SET MajidDenomination.Amount = OX.Amount
FROM OPENXML(@h,N'/root/majid/Denomination')
WITH MajidDenomination OX
WHERE MajidDenomination.CurrencyNumber = OX.CurrencyNumber AND
MajidDenomination.Label = OX.Label
EXEC sp_xml_removedocument @h

Also I would like to know what flag option (0,1,2,8) I should use in OPENXML call so the table column value which is not given in xml format not beeing replace with NULL."

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-10-21 : 10:18:58

replace Denomination with MajidDenomination in your xml.

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources