"Jonathan Yong" <jyong@someplace.net> wrote in message
news:%23v4reVnsEHA.3324@TK2MSFTNGP15.phx.gbl...
>
> You have reverse the way you specify column to xml attribute mapping. It
> should be like this :
>
> SELECT xmlTables.outcome_id,
> xmlTables.chvOutcomeDte,
> xmlTables.iPatRegId,
> xmlTables.iRegimenId,
> xmlTables.chvRecordType
> FROM OPENXML (@DocHandle, '/DATA/xmlRow',1) WITH
> (outcome_id int '@outcome_id',
> chvOutcomeDte varchar(20) '@dt_outcome',
> iPatRegId int '@patient_regimen_id',
> iRegimenId int '@regimen_id',
> chvRecordType varchar(20) '@record_type') AS xmlTables
>
>
> Irishmaninusa wrote:
>> I have the following piece of code
>>
>> -- 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" /><xmlRow
>> outcome_id="0" dt_outcome="" patient_regimen_id="20" regimen_id="2"
>> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
>> patient_regimen_id="19" regimen_id="2" record_type="update" /><xmlRow
>> outcome_id="0" dt_outcome="" patient_regimen_id="18" regimen_id="2"
>> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
>> patient_regimen_id="17" regimen_id="2" record_type="update" /><xmlRow
>> outcome_id="0" dt_outcome="" patient_regimen_id="5" regimen_id="2"
>> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
>> patient_regimen_id="4" regimen_id="2" record_type="update" /><xmlRow
>> outcome_id="0" dt_outcome="" patient_regimen_id="2" regimen_id="2"
>> record_type="update" /></DATA>'
>> EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlTable
>>
>>
>>
>> SELECT xmlTables.outcome_id,
>> xmlTables.dt_outcome,
>> xmlTables.patient_regimen_id,
>> xmlTables.regimen_id,
>> xmlTables.record_type
>> FROM OPENXML (@DocHandle, '/DATA/xmlRow',1) WITH
>> (outcome_id int '@outcome_id',
>> dt_outcome varchar(20) '@chvOutcomeDte',
>> patient_regimen_id int '@iPatRegId',
>> regimen_id int '@iRegimenId',
>> record_type varchar(20) '@chvRecordType') AS xmlTables
>>
>>
>> -- Close the xml table
>> EXEC sp_xml_removedocument @DocHandle
>>
>>
>> and in the results the only one I get is is the outcome_id value, but the
>> rest of them are coming back as null, any reason why this is happening?
>>

Irishmaninusa wrote:
> I have the following piece of code
>
> -- 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" /><xmlRow
> outcome_id="0" dt_outcome="" patient_regimen_id="20" regimen_id="2"
> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
> patient_regimen_id="19" regimen_id="2" record_type="update" /><xmlRow
> outcome_id="0" dt_outcome="" patient_regimen_id="18" regimen_id="2"
> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
> patient_regimen_id="17" regimen_id="2" record_type="update" /><xmlRow
> outcome_id="0" dt_outcome="" patient_regimen_id="5" regimen_id="2"
> record_type="update" /><xmlRow outcome_id="0" dt_outcome=""
> patient_regimen_id="4" regimen_id="2" record_type="update" /><xmlRow
> outcome_id="0" dt_outcome="" patient_regimen_id="2" regimen_id="2"
> record_type="update" /></DATA>'
> EXEC sp_xml_preparedocument @DocHandle OUTPUT, @xmlTable
>
>
>
> SELECT xmlTables.outcome_id,
> xmlTables.dt_outcome,
> xmlTables.patient_regimen_id,
> xmlTables.regimen_id,
> xmlTables.record_type
> FROM OPENXML (@DocHandle, '/DATA/xmlRow',1) WITH
> (outcome_id int '@outcome_id',
> dt_outcome varchar(20) '@chvOutcomeDte',
> patient_regimen_id int '@iPatRegId',
> regimen_id int '@iRegimenId',
> record_type varchar(20) '@chvRecordType') AS xmlTables
>
>
> -- Close the xml table
> EXEC sp_xml_removedocument @DocHandle
>
>
> and in the results the only one I get is is the outcome_id value, but the
> rest of them are coming back as null, any reason why this is happening?