OPENXML INSERT/UPDATE and NTEXT

Hi,
I'm having problems inserting/updating a NTEXT field using OPENXML.
The field always gets a blank value with any parameters it receives.
Here's the (simplified) query:
---
declare @.doc int
-- Actually, it's a parameter
declare @.xml nvarchar(4000);
set @.xml = N'
<Article>
<Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
<Content>yada yada yada</Content>
</Article>'
EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3) WITH Article new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM OPENXML(@.doc, 'Article', 3) WITH Article
EXEC sp_xml_removedocument @.doc
---
Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
value.
Any ideas?
DiegoI think you need to revise your OPENXML syntax a bit. Try the following
instead of what you have:
UPDATE Article
SET Content = new.Content
FROM OPENXML(@.doc, 'Article', 3)
WITH (
id uniqueidentifier 'Id',
content nvarchar(400) 'Content'
) new
WHERE Article.Id = new.Id
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Diego Mijelshon" <no@.thanks.com> wrote in message
news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
> Hi,
> I'm having problems inserting/updating a NTEXT field using OPENXML.
> The field always gets a blank value with any parameters it receives.
> Here's the (simplified) query:
> ---
> declare @.doc int
> -- Actually, it's a parameter
> declare @.xml nvarchar(4000);
> set @.xml = N'
> <Article>
> <Id>be60839f-cc33-4a9f-af91-e3bbcb7617ac</Id>
> <Content>yada yada yada</Content>
> </Article>'
> EXEC sp_xml_preparedocument @.doc OUTPUT, @.xml
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3) WITH Article new
> WHERE Article.Id = new.Id
> IF @.@.rowcount = 0
> INSERT INTO Article
> SELECT *
> FROM OPENXML(@.doc, 'Article', 3) WITH Article
> EXEC sp_xml_removedocument @.doc
> ---
> Oddly, "SELECT * FROM OPENXML(@.doc, 'Article', 3) WITH Article" shows the
> value.
> Any ideas?
> Diego
>|||Adam,
Thanks for your answer, but that didn't fix it.
Besides, the beauty in the "WITH tablename" clause, combined with FOR XML
AUTO, is that I get "free" O/R-M using XML Serializing.
I'll share my solution. It still looks like a bug to me...
---
SELECT *
INTO ##tmp
FROM OPENXML(@.doc, 'Article', 3) WITH Article
UPDATE Article
SET Content = new.Content
FROM ##tmp new
WHERE Article.Id = new.Id
IF @.@.rowcount = 0
INSERT INTO Article
SELECT *
FROM ##tmp
DROP TABLE ##tmp
---
As you can see, inserting in a temporal table does the trick.
Diego
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OxCpmzEGFHA.1476@.TK2MSFTNGP09.phx.gbl...
> I think you need to revise your OPENXML syntax a bit. Try the following
> instead of what you have:
>
> UPDATE Article
> SET Content = new.Content
> FROM OPENXML(@.doc, 'Article', 3)
> WITH (
> id uniqueidentifier 'Id',
> content nvarchar(400) 'Content'
> ) new
> WHERE Article.Id = new.Id
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:ei8UgeEGFHA.1188@.tk2msftngp13.phx.gbl...
the
>|||"Diego Mijelshon" <no@.thanks.com> wrote in message
news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> DROP TABLE ##tmp
> ---
> As you can see, inserting in a temporal table does the trick.
I'm glad you found something that works. FYI, you should probably use a
local temporary table (single #) instead of a global one (##) unless you
need access to this same temp table from other processes...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uWtvvjIGFHA.560@.TK2MSFTNGP15.phx.gbl...
> "Diego Mijelshon" <no@.thanks.com> wrote in message
> news:um$u8aIGFHA.548@.TK2MSFTNGP14.phx.gbl...
> I'm glad you found something that works. FYI, you should probably use a
> local temporary table (single #) instead of a global one (##) unless you
> need access to this same temp table from other processes...
Thanks for the correction, I had it backwards :-)
Diego