Gurus, we are creating a E2B XML reading data from an Oracle Argus DB.
Problem is in the drug tag.
The drug tag looks like this. i.e. they gave the format like this:
<drug>
<medicinalproduct>[get from CASE_PRODUCT.PRODUCT_NAME]</medicinalproduct>
<drugdosagetext>[get from CASE_DOSE_REGIMENS.DOSE_DESCRIPTION]</drugdosagetext>
</drug>
Problem is for a particular case ID, there are more than one value for some drugs for the above tags.
So, basically we get a XML like:
<drug>
<medicinalproduct>product1</medicinalproduct>
<drugdosagetext>dose descriptin 1</drugdosagetext>
<drugdosagetext>dose descriptin 2</drugdosagetext>
</drug>
Of course the program gives an error for drugdosagetext: ORA-01427: single-row subquery returns more than one row
This happens for lot of other tags as well.
Some tags have XMLELEMENT, like this one:
SELECT
XMLELEMENT(
"drugdosagetext"
,dose_description
)FROM case_dose_regimens a
WHERE a.case_id = p_case_id
AND a.seq_num = c.seq_num
and others have XMLFOREST:
SELECT
XMLFOREST
(
to_Char(a.stop_datetime,'YYYYMMDD') AS "drugenddate"
,a.duration_seconds AS "drugtreatmentduration"
,'?????' AS "drugcharacterization"
,'?????' AS "obtaindrugcountry"
,'?????' AS "drugauthorizationholder"
,a.dose AS "drugstructuredosagenumb"
)FROM case_dose_regimens a
WHERE a.case_id = p_case_id
AND a.seq_num = c.seq_num
Both throw the ORA-01427.
My question is:
(1.) Is this below XML valid.
<drug>
<medicinalproduct>product1</medicinalproduct>
<drugdosagetext>dose descriptin 1</drugdosagetext>
<drugdosagetext>dose descriptin 2</drugdosagetext>
</drug>
Accoring to what I read in Wikipedia, the above is a NOT a well formed XML document, since the children, drugdosagetext , are not enclosed in a parent tag? Am I correct?,
(2.) Is the specification we wre given, wrong in this case? i.e. If the values are repeating, they should have given us the parent tag to put the repeaing values to? Am I correct?