如何结合不同的模式 [英] how to combine different schemas

问题描述

我正在使用自定义输出从我的平面数据"生成XML，如下所示:

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");

生成如下文件:

<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

如何基于ID连接两组文件?

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

我想要的结果是:

<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>

推荐答案

如果有2个文件，则可以简单地提取两个文件(使用id)

If you have the 2 files, you can simple extract both (using id)

DECLARE @patient string ="/patient/{Id}.tsv";
DECLARE @charge string ="/charge/{Id}.tsv";

@patients =
EXTRACT Id string, content string FROM @patient USING Extractors.Text();

@charges =
EXTRACT Id string, content string FROM @charge USING Extractors.Text();

然后，您可以通过id简单地加入并连接患者和收费并输出.

Then you can simple join by id and concatenate patients and charges and output it.

这篇关于如何结合不同的模式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！