473,288 Members | 1,726 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

OpenXML Query

jrd
The below code is only pulling the outcome_id value, but is pulling
back null for the other fields in the xml string, any ideas on what is
going on?

-- Prepare xml data to be transfered into an xml table in sql server
DECLARE @xmlTable varchar(8000)
DECLARE @DocHandle int
select @xmlTable = '<DATA><xmlRow outcome_id="35"
dt_outcome="12/1/2004" patient_regimen_id="21" regimen_id="2"
record_type="existing" /></DATA>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlTable
SELECT xmlTables.*
FROM OPENXML (@DocHandle, '/DATA/xmlRow') WITH
(outcome_id int '@outcome_id',
patient varchar '@chvOutcomeDte') AS xmlTables
-- Close the xml table
EXEC sp_xml_removedocument @DocHandle
I did have this working in another stored procedure, but this one does
not seem to want to work. I really need some help on this, thanks.
Jul 20 '05 #1
1 2654

"jrd" <jd***@structureinteractive.com> wrote in message
news:92**************************@posting.google.c om...
The below code is only pulling the outcome_id value, but is pulling
back null for the other fields in the xml string, any ideas on what is
going on?

-- Prepare xml data to be transfered into an xml table in sql server
DECLARE @xmlTable varchar(8000)
DECLARE @DocHandle int
select @xmlTable = '<DATA><xmlRow outcome_id="35"
dt_outcome="12/1/2004" patient_regimen_id="21" regimen_id="2"
record_type="existing" /></DATA>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlTable
SELECT xmlTables.*
FROM OPENXML (@DocHandle, '/DATA/xmlRow') WITH
(outcome_id int '@outcome_id',
patient varchar '@chvOutcomeDte') AS xmlTables
-- Close the xml table
EXEC sp_xml_removedocument @DocHandle
I did have this working in another stored procedure, but this one does
not seem to want to work. I really need some help on this, thanks.


There's no attribute called patient or @chvOutcomDte in your XML, so I'm not
sure what you're expecting to get in the second column. This works, for
example:

SELECT xmlTables.*
FROM OPENXML (@DocHandle, '/DATA/xmlRow') WITH
(outcome_id int,
dt_outcome datetime) AS xmlTables

You should also specify the length of your varchar - with no length, it
defaults to varchar(1), so you might get data truncation.

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: JM | last post by:
I have a SP set up to take an input param and then fire the following sp_xml_preparedocument OPENXML (With a select statement) sp_xml_removedocument This works fine with the XML syntax that...
2
by: Bostonasian | last post by:
I've got thousands of XML docs and have to import those to the DB. And I am having problem with getting some values because of tricky XML format. I didn't create this XML format and I don't like...
1
by: Sandy | last post by:
Hi, My application writes data into sql server. Currently it converts data into XML (an in memory XML string) and write using OPENXML. I want to know if i write it to a csv file and use BCP,...
1
by: a | last post by:
I'm trying to pass an xml file as a variable of type string to an OpenXML stored procedure, but the code below fails at the "sCmd01.ExecuteNonQuery();" line. The SPROC is working fine in Query...
0
by: Micke | last post by:
On a code behing page to an aspx page I want to open a xml file in Excel and save it as an Excel file. I have following code that is working: Dim oExcel As New Excel.Application Dim oBook As...
1
by: figital | last post by:
I am researching the use of OpenXml for doing mass updates/inserts. Does anyone know how this procedure works as far as sql injection is concerned? I've always been taught to use sp's with...
2
by: Shilpa | last post by:
Hi All, I want to pass XML and the data in the XML should be stored in the tables of the database. However, I do not want to use the OpenXML statement. Please let me know. Regards, Shilpa
9
by: anupamjain | last post by:
Hi, After 2 weeks of search/hit-and-trial I finally thought to revert to the group to find solution to my problem.(something I should have done much earlier) This is the deal : On a JSP...
1
by: yingwen | last post by:
I have a xml similar like this: <Member ID="123"> <DateBorrowed>11-01-2006</DateBorrowed> <Book ID="222" Title="ABC"> <Category> Fiction</Category> </Book>...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.