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.

[Simon Hayes]

"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