how to combine different schemas

2019-09-20 09:28发布

问题:

I'm using a custom OUTPUTTER to generate XML from my "flat data" like so:

SELECT *..
OUTPUT @all_data
TO "/patient/{ID}.tsv"
USING new Microsoft.Analytics.Samples.Formats.Xml.XmlOutputter("Patient");

Which generates individual files that look like this:

<Patient>
    <ID>5283293478</ID>
    <ANESTHESIA_START>09/06/2019 11:52:00</ANESTHESIA_START>
    <ANESHTHESIA_END>09/06/2019 14:40:00</ANESHTHESIA_END>
    <SURGERY_START_TIME>9/6/2019 11:52:00 AM</SURGERY_START_TIME>
    <SURGERY_END_TIME>9/6/2019 2:34:00 PM</SURGERY_END_TIME>
    <INCISION_START>9/6/2019 12:45:00 PM</INCISION_START>
    <INCISION_END>9/6/2019 2:18:00 PM</INCISION_END>
</Patient>

A separate script is generating data like this:

SELECT *..
OUTPUT @other_data
TO "/charge/{ID}.tsv"
USING new Microsoft.Analytics.Samples.Formats.Xml.XmlOutputter("Patient");

Yielding files that look like this:

<Charge>
    <ID>5283293478</ID>
    <PROVIDER_TYPE>CRNA</PROVIDER_TYPE>
</Charge>
<Charge>
    <ID>5283293478</ID>
    <PROVIDER_TYPE>Student Nurse Anesthetist</PROVIDER_TYPE>
</Charge>

As you can see, the files that are being created are:

/patient/{ID}.tsv
/charge/{ID}.tsv

How do I concatenate the two sets of files based on ID?

The result I'd like is:

<Patient>
    <ID>5283293478</ID>
    <ANESTHESIA_START>09/06/2019 11:52:00</ANESTHESIA_START>
    <ANESHTHESIA_END>09/06/2019 14:40:00</ANESHTHESIA_END>
    <SURGERY_START_TIME>9/6/2019 11:52:00 AM</SURGERY_START_TIME>
    <SURGERY_END_TIME>9/6/2019 2:34:00 PM</SURGERY_END_TIME>
    <INCISION_START>9/6/2019 12:45:00 PM</INCISION_START>
    <INCISION_END>9/6/2019 2:18:00 PM</INCISION_END>
</Patient>
<Charge>
    <ID>5283293478</ID>
    <PROVIDER_TYPE>CRNA</PROVIDER_TYPE>
</Charge>
<Charge>
    <ID>5283293478</ID>
    <PROVIDER_TYPE>Student Nurse Anesthetist</PROVIDER_TYPE>
</Charge>