?
Solved

OpenXML

Posted on 2003-03-18
11
Medium Priority
?
1,333 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
[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
  • 5
  • 4
11 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
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!

 

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…

752 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