Solved

OPENXML is inserting NULL values

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

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
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

803 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