OpenXML Query -- sql server (alternate)

jdaly NO[at]SPAM structureinteractive.com
10/14/2004 8:58:03 PM

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

Simon Hayes
10/15/2004 4:08:42 PM

[quoted text, click to view]

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

sql server (alternate) : OpenXML Query