Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

OpenXML

Posted on 2003-03-18
11
Medium Priority
?
1,425 Views
Last Modified: 2007-12-19
How do I add a value that is not from my XML doc? For example, I want to put the value 6 in Qty field but it is expecting a Node instead of @num1.

DECLARE @num1 int
SET @num1 = 6

EXEC sp_xml_preparedocument @docID OUTPUT, @XmlDoc
INSERT INTO Table1
SELECT *
FROM OPENXML (@docID, '/ROOT/Customer/Order/OrderDetail',0)
    WITH (CustomerID  varchar(10) '../@CustomerID',
          OrderDate   datetime    '../@OrderDate',
          ProdID      int         '@ProductID',
          Qty         int         @num1)
EXEC sp_xml_removedocument @docID

0
Comment
Question by:MarkAOP
  • 5
  • 4
10 Comments
 

Author Comment

by:MarkAOP
ID: 8162762
Please help!
0
 
LVL 9

Expert Comment

by:rherguth
ID: 8163164
Is this what you need? (SELECT line has been modified)

DECLARE @num1 int
SET @num1 = 6

EXEC sp_xml_preparedocument @docID OUTPUT, @XmlDoc
INSERT INTO Table1 (CustomerID, OrderDate, ProdID, Qty)
SELECT CustomerID, OrderDate, ProdID, @num1
FROM OPENXML (@docID, '/ROOT/Customer/Order/OrderDetail',0)
   WITH (CustomerID  varchar(10) '../@CustomerID',
         OrderDate   datetime    '../@OrderDate',
         ProdID      int         '@ProductID',
         Qty         int         @num1)
EXEC sp_xml_removedocument @docID

I also modified the INSERT line, but it's optional.  There have been times where I've regretted not specifing the fields in the INSERT and had the column order change (added column) which would break the first version, but not the second.
0
 
LVL 9

Expert Comment

by:rherguth
ID: 8163183
Sorry, I should have also removed a line from the WITH if Qty is not valid.  If it is valid, you could leave it and it would be overlooked by the INSERT.

WITH (CustomerID  varchar(10) '../@CustomerID',
        OrderDate   datetime    '../@OrderDate',
        ProdID      int         '@ProductID')
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:MarkAOP
ID: 8167492
The problem is that I get an error saying - XML Parsing Error: expression does not return a DOM node. -->6<--

It's expecting a DOM node but I have a value that is not in the XML string.
0
 
LVL 9

Expert Comment

by:rherguth
ID: 8171972
This works for me under SQL 2000 SP3 with SQLXMLv3 Installed and MSXML4 SP1:

DECLARE @num1 int
SET @num1 = 6

EXEC sp_xml_preparedocument @docID OUTPUT, @XmlDoc
INSERT INTO Table1 (CustomerID, OrderDate, ProdID, Qty)
SELECT CustomerID, OrderDate, ProdID, @num1 [Qty]
Table1
FROM OPENXML (@docID, '/ROOT/Customer/Order/OrderDetail',0)
  WITH (CustomerID  varchar(10) '../@CustomerID',
        OrderDate   datetime    '../@OrderDate',
        ProdID      int         '@ProductID')
EXEC sp_xml_removedocument @docID

The problem with the earlier code was the @num1 needed a column alias and the "Qty int @num1)" line needs to be removed.
0
 
LVL 9

Accepted Solution

by:
rherguth earned 220 total points
ID: 8171989
I don't know how that bloody Table1 got inserted into the middle of my code, but it doesn't belong there.  Sorry.

DECLARE @num1 int
SET @num1 = 6

EXEC sp_xml_preparedocument @docID OUTPUT, @XmlDoc
INSERT INTO Table1 (CustomerID, OrderDate, ProdID, Qty)
SELECT CustomerID, OrderDate, ProdID, @num1 [Qty]
FROM OPENXML (@docID, '/ROOT/Customer/Order/OrderDetail',0)
 WITH (CustomerID  varchar(10) '../@CustomerID',
       OrderDate   datetime    '../@OrderDate',
       ProdID      int         '@ProductID')
EXEC sp_xml_removedocument @docID
0
 

Author Comment

by:MarkAOP
ID: 8174903
It does work but only when the variable is not in the With section, thanks.
0
 
LVL 9

Expert Comment

by:rherguth
ID: 8175064
Did you need the variable to work in the WITH section?
0
 

Author Comment

by:MarkAOP
ID: 8175332
No, I just needed to insert some data that was not from the xml string and the WITH section only wants an Xpath string. Everything is working the way I needed it to, thanks for your help.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 10348347
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: rherguth {http:#8171989}

Please leave any comments here within the next four days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

walterecook
EE Cleanup Volunteer
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

What we learned in Webroot's webinar on multi-vector protection.
Blockchain technology enhances society similar to the Internet. Its effects are broad, disruptive, and will boost global productivity.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

581 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