Home » Developer & Programmer » JDeveloper, Java & XML » XML Files Generation (LINUX)
XML Files Generation [message #337702] Thu, 31 July 2008 14:14 Go to next message
jango77
Messages: 4
Registered: July 2008
Junior Member
Hello,

I have a problem creating an XML file from a join involving about ten tables. We are on Oracle 10g 10.2.0.4. My File will have to look like the following:-

<?xml version="1.0" encloding="utf-8"?>
<TRACS type="XXXX" data="XXXXX" version="1.0" action-code="ADD">
<department>
<department-id>XXXX</department_id>
<employee>
<first-name>JJJJJ</first_name>
<last-name>LLLL</last_name>
<encounter measure="MMM">
<st-date>04-02-2007</st-date>
<emp-id>92993</emp-id>
<emp-ssn>388299289</emp-ssn>
</encounter>
</employee>
<first-name>EEEE</first_name>
<last-name>WWWW</last_name>
<encounter measure="MMM">
<st-date>04-22-2007</st-date>
<emp-id>92993</emp-id>
<emp-ssn>388299289</emp-ssn>
</encounter>
</employee>
</department>
</TRACS>

The second line is what I have a problem with. I have tried doing the following:
SELECT XMLElement( "TRACS",XMLAttributes(''OUTPATIENT'' as "type", ''CLINICAL'' as "data", ''1.0'' as "version", ''ADD'' as "action-code"), (XMLELEMENT("provider", XMLForest(DECODE(a13.HOSPITAL_ID,''C'',110078,''E'',110010) as "provider-id"),xmlagg(XMLELEMENT( "patient", XMLForest( a18.PATIENT_FIRST_NM "first-name", a18.PATIENT_LAST_NM "last-name" ),XMLELEMENT("encounter", XMLATTRIBUTES ( ''HOP-AMI'' AS "measure-set"), XMLForest ( a110.DAY_ADMIT_DESC as "encounter-date", a18.PATIENT_ID as "patient-id",
a12.MEDICAL_RECORD_NBR as "medical-record-nbr"))))))) as "provider"....

but it repeats the header for each record. I want just ONE such header for the whole document. I am writing this info into a CLOB column in a table first.

Please let me know if you have something that will work.
Thanks

jango77

Report message to a moderator

Re: XML Files Generation [message #338059 is a reply to message #337702] Sat, 02 August 2008 21:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
might be easier to help you if you posted a working example for use to modify.

Try refactoring your query using a nested select of some sort, such that you select from dual as the outer query. The problem item will still repeat for each row but there will only be one row.

Kevin

Report message to a moderator

Re: XML Files Generation [message #338306 is a reply to message #338059] Mon, 04 August 2008 08:48 Go to previous messageGo to next message
jango77
Messages: 4
Registered: July 2008
Junior Member
Well, here it is.
Declare
result CLOB;
--
Ctx dbms_xmlgen.ctxHandle;
begin
ctx := dbms_xmlgen.newcontext('SELECT /*+ FIRST_ROWS PARALLEL(a11,16) PARALLEL(a12, 16)*/ XMLELEMENT("provider",

XMLForest(DECODE(a13.HOSPITAL_ID,''C'',110078,''E'',110010) as "provider-id"),xmlagg(XMLELEMENT( "patient", XMLForest ( a111.PATIENT_FIRST_NM "first-name",
a111.PATIENT_LAST_NM "last-name" ),XMLELEMENT("encounter", XMLATTRIBUTES ( ''HOP-SURGERY'' AS "measure-set"),
XMLForest ( a113.DAY_ADMIT_DESC as "encounter-date",
a110.PLAN_INSURANCE_CARD_NBR "patient-HIC-Number",
a111.PATIENT_ID as "patient-id",
a12.MEDICAL_RECORD_NBR as "medical-record-nbr")
))))
from EHCDWH.FACT_CHARGE_HOSPITAL a11
join EHCVW.LKP_ENCOUNTER a12 on (a11.ENCOUNTER_KEY = a12.ENCOUNTER_KEY)
join EHCDWH.LKP_SPECIALTY_HOSPITAL a13 on (a12.SPECIALTY_ADMIT_KEY = a13.SPECIALTY_HOSPITAL_KEY)
join EHCVW.LKP_PROCEDURE a14 on (a11.PROCEDURE_KEY = a14.PROCEDURE_KEY)
join EHCVW.LKP_PROCEDURE_CPT a15 on (a14.PROCEDURE_CPT_KEY = a15.PROCEDURE_CPT_KEY)
join EHCDWH.LKP_DATA_SOURCE a16 on (a11.DATA_SOURCE_KEY = a16.DATA_SOURCE_KEY)
join EHCVW.LKP_BILL_HOSPITAL a17 on (a11.ENCOUNTER_KEY = a17.ENCOUNTER_KEY)
join EHCDWH.LKP_CLASS_FINANCIAL_HOSPITAL a18 on (a17.CLASS_FINANCIAL_HOSPITAL_KEY =a18.CLASS_FINANCIAL_HOSPITAL_KEY)
join EHCDWH.LKP_ENTITY_HEALTHCARE a19 on (a11.ENTITY_HEALTHCARE_KEY = a19.ENTITY_HEALTHCARE_KEY)
join EHCVW.REL_ENCOUNTER_PLAN a110 on (a11.ENCOUNTER_KEY = a110.ENCOUNTER_HOSPITAL_KEY)
join EHCVW.LKP_PATIENT a111 on (a11.PATIENT_KEY = a111.PATIENT_KEY)
join EHCDWH.LKP_TYPE_ENCOUNTER a112 on (a12.TYPE_ENCOUNTER_KEY = a112.TYPE_ENCOUNTER_KEY)
join EHCVW.LKP_DAY_ADMIT a113 on (a12.DAY_ADMIT_KEY = a113.DAY_ADMIT_KEY)
where a113.MONTH_ADMIT_KEY in (200804, 200805)
and a13.HOSPITAL_ID in (''C'', ''E'')
and a112.CLASS_ENCOUNTER_ID in (''O'')
and a15.PROCEDURE_CPT_CD between ''21454'' and ''63075''
and a19.ENTITY_HEALTHCARE_ID in (''589743^ENT'', ''589745^ENT'', ''589744^ENT'')
and a16.DATA_SOURCE_ID in (''670654'', ''670653'', ''670621'')
and rownum < 10
group by DECODE(a13.HOSPITAL_ID,''C'',110078, ''E'', 110010), a111.PATIENT_FIRST_NM,
a111.PATIENT_LAST_NM,a113.DAY_ADMIT_DESC,
a111.PATIENT_ID , a12.MEDICAL_RECORD_NBR order by DECODE(a13.HOSPITAL_ID,''C'',110078, ''E'', 110010), a111.PATIENT_FIRST_NM,a111.PATIENT_LAST_NM');
--
dbms_xmlgen.setNullHandling(ctx,dbms_xmlgen.empty_tag);

dbms_xmlgen.setRowsetTag(ctx,'provider');
dbms_xmlgen.setRowTag(ctx,'');
result := DBMS_XMLGEN.getXML(Ctx);
--
INSERT INTO UHC_DATA_CLOB VALUES ('HIC',result, sysdate);
commit;
DBMS_XMLGEN.closeContext(Ctx);

end;

What I want is something like the following:-

<?xml version="1.0"?>
<provider>
<provider-id>110078</provider-id>
<patient> <first-name>BRITTANY</first-name>
<last-name>THOMAS</last-name>
<encounter measure-set="HOP-SURGERY">
<encounter-date>05/31/2008</encounter-date>
<patient-HIC-Number>111745603216</patient-HIC-Number>
<patient-id>41987428</patient-id>
<medical-record-nbr>1673807</medical-record-nbr>
</encounter> </patient> <patient>
<first-name>BRITTNEY</first-name>
<last-name>RABOTTE</last-name>
<encounter measure-set="HOP-SURGERY">
<encounter-date>05/31/2008</encounter-date>
<patient-HIC-Number>111374011334</patient-HIC-Number>
<patient-id>44491968</patient-id>
<medical-record-nbr>1687914</medical-record-nbr>
</encounter>
</patient>
</provider>

Hope you have a solution to this issue...

Thanks

Report message to a moderator

Re: XML Files Generation [message #338307 is a reply to message #337702] Mon, 04 August 2008 09:07 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
have about sharing some table creates and insert statements. No one will write any code on your behalf to demonstrate ideas, without these.

Kevin

Report message to a moderator

Re: XML Files Generation [message #338312 is a reply to message #338307] Mon, 04 August 2008 09:43 Go to previous messageGo to next message
jango77
Messages: 4
Registered: July 2008
Junior Member
I don't know if I am understanding you. Do you want me to send you Create and Insert stmts for all the embedded tables in the Query or just the one for the lone XML document insert?

The gist of it is I am getting three "provider" tags when I only need one. I can't set setrowsettag and setrowtag both to null. The tags inside are the problem tags.

Do let me know what exactly you are looking for and I will try to comply.

Thx

Report message to a moderator

Re: XML Files Generation [message #338321 is a reply to message #337702] Mon, 04 August 2008 10:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am sorry but I am not really following your problem. Why does this not work for you?

SQL> 
SQL> select dbms_xmlgen.getxml
  2  (
  3    'select 110078 provider_id'
  4  ||'      ,cursor('
  5  ||'               select ''Brittany'' first_name,''Thomas'' last_name'
  6  ||'                     ,cursor('
  7  ||'                             select ''HOP-SURGERY'' measure_set,''05/31/2008'' encounter_date,111745603216 patient_hic_number'
  8  ||'                             from dual'
  9  ||'                            ) encounter'
 10  ||'               from dual'
 11  ||'             ) patient'
 12  ||' from dual'
 13  )
 14  from dual
 15  /

DBMS_XMLGEN.GETXML('SELECT110078PROVIDER_ID'||',CURSOR('||'SELECT''BRITTANY''FIR
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <PROVIDER_ID>110078</PROVIDER_ID>
  <PATIENT>
   <PATIENT_ROW>
    <FIRST_NAME>Brittany</FIRST_NAME>
    <LAST_NAME>Thomas</LAST_NAME>
    <ENCOUNTER>
     <ENCOUNTER_ROW>
      <MEASURE_SET>HOP-SURGERY</MEASURE_SET>
      <ENCOUNTER_DATE>05/31/2008</ENCOUNTER_DATE>
      <PATIENT_HIC_NUMBER>111745603216</PATIENT_HIC_NUMBER>
     </ENCOUNTER_ROW>
    </ENCOUNTER>
   </PATIENT_ROW>
  </PATIENT>
 </ROW>
</ROWSET>


1 row selected.

Kevin

Report message to a moderator

Re: XML Files Generation [message #338334 is a reply to message #338321] Mon, 04 August 2008 11:02 Go to previous message
jango77
Messages: 4
Registered: July 2008
Junior Member
Well, the tag "encounter" should exactly look like the following:-

<encounter measure-set="HOPI-SURGERY">
.....
</encounter>

It should have the value measure-set="HOPI-SURGERY" as its attributes.

I have something like a 15 table join and I am not sure if this will do it. I am going to try it nonetheless.

Thanks

Report message to a moderator

Previous Topic: java.lang.incombatible class change error
Next Topic: Javascript drag and drop with Oracle
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Nov 21 16:44:59 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.