Solved

OPENXML is inserting NULL values

Posted on 2006-11-15
8
1,379 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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
Comment Utility
Why is that returning NULL values? My sample XML has values for the corresponding elements
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
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
 

Author Comment

by:rxraza
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

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

Accepted Solution

by:
Anthony Perkins earned 150 total points
Comment Utility
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
Comment Utility
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
Comment Utility
that was parlty typo and partly XPATH
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Sharepoint 3.0 migration 4 37
Selecting specific rows 3 33
Numeric sequence in SQL 14 36
SQL Split character from numbers 3 16
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now