[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

OPENXML is inserting NULL values

Posted on 2006-11-15
8
Medium Priority
?
1,584 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

650 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