?
Solved

OPENXML is inserting NULL values

Posted on 2006-11-15
8
Medium Priority
?
1,531 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 400 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 600 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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

770 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