Solved

OPENXML is inserting NULL values

Posted on 2006-11-15
8
1,429 Views
Last Modified: 2008-02-01
Hi folks:

I have the following insert statement which I scraped out from a stored procedure on SQL SERVER 2000

INSERT INTO OrderLineItems
      SELECT nullif(OrderID,0), nullif(LineItemID,0), nullif(LineItemCode,''), nullif(LineItemPrice,0.0), nullif(LineItemQty,0)
            FROM OPENXML (@idoc, '/Order/Items/',2)
                  WITH  ( OrderID int, LineItemID int, LineItemCode varchar(20), LineItemPrice money, LineItemQty int)

It is giving me the following error

Cannot insert the value NULL into column 'LineItemID', table 'Product.dbo.OrderLineItems'; column does not allow nulls. INSERT fails.

The OpenXML should get the values from the XML node and just not simply NULL values

Here is the sample XML that I am using

exec CreateOrder '<Order><Items>
                        <Item>
                              <OrderID>1</OrderID>
                              <LineItemID>1</LineItemID>
                              <LineItemCode>ITM-001</LineItemCode>
                              <LineItemPrice>99.00</LineItemPrice>
                              <LineItemQty>1</LineItemQty>
                              
                        </Item>
                  </Items>
                  </Order>'

Is there anything I am doing wrong ?

Following lies the entire sp

CREATE PROCEDURE CreateOrder @XMLString as text AS

DECLARE @idoc int
DECLARE @doc varchar (1000)

DECLARE @OrderID as int
DECLARE @LineItemID as int

SET @OrderID = 1000
SET @LineItemID = 90


--PRINT @XMLString

--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

IF @@ERROR <> 0
      --PRINT @@Error
      RAISERROR ('Error: An error occurred',10,1)

-- Execute a SELECT statement that uses the OPENXML rowset provider
INSERT INTO OrderLineItems
      SELECT nullif(OrderID,0), nullif(LineItemID,0), nullif(LineItemCode,''), nullif(LineItemPrice,0.0), nullif(LineItemQty,0)
            FROM OPENXML (@idoc, '/Order/Items/',2)
                  WITH  ( OrderID int, LineItemID int, LineItemCode varchar(20), LineItemPrice money, LineItemQty int)

IF @@ERROR <> 0
      --PRINT @@Error
      RAISERROR ('Error: An error occurred',10,1)

EXEC sp_xml_removedocument @idoc
GO

Thanks in advance
0
Comment
Question by:rxraza
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17949888
if you remove the (insert into) part, but keep the SELECT part with the openxml, you will see that your query only returns null values.

looking for the correct syntax...
0
 

Author Comment

by:rxraza
ID: 17949940
Why is that returning NULL values? My sample XML has values for the corresponding elements
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
ID: 17950211
it took me some time, but I found YOUR error (typo)

look at the procedure declaration:
CREATE PROCEDURE CreateOrder @XMLString as text AS

now, look at the following line:
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc




if it did not yet make "DUH !!!", let me correct your second line:
EXEC sp_xml_preparedocument @XMLString OUTPUT, @doc

and it should work fine...
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:rxraza
ID: 17951647
Well, Thanks for pointing out the typo. I guess it should have been

EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLString

I ran the sp with above changes and guess what still it would say the same message

Cannot insert the value NULL into column 'LineItemID', table 'Product.dbo.OrderLineItems'; column does not allow nulls. INSERT fails.

0
 

Author Comment

by:rxraza
ID: 17951951
I am increasing the points for some more expert opinions
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
ID: 17953084
You XPath is incorrect it should be:
SELECT      nullif(OrderID,0), nullif(LineItemID,0), nullif(LineItemCode,''), nullif(LineItemPrice,0.0), nullif(LineItemQty,0)
FROM      OPENXML (@idoc, '/Order/Items/Item',2) WITH (
                  OrderID int, LineItemID int, LineItemCode varchar(20), LineItemPrice money, LineItemQty int)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 17953092
Also, please attend to the following questions as they are now considered abandoned:
1 10/05/2006 500 updating MS DNS thru ASP.NET  Open ASP.NET
2 10/06/2006 500 error from the sample scrpt @ http://win...  Open Visual Basic
0
 

Author Comment

by:rxraza
ID: 17953174
that was parlty typo and partly XPATH
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL: Get Holding Day Based On Transfer Date And Return Date 13 74
SQL Server / Update DB? 22 38
SQL Log size 3 18
MSSQL - Lock Row from reading by other programs 9 39
Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

827 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question