12 Replies Latest reply on May 7, 2019 5:36 PM by tsuji

    How to use XMLQuery to retrieve info from R3 XML

    3829237

      I am using XMLTABLE and XML namespace for R2 XMLs, which are generally formatted like this:

      <mhlwadminitemsicsr lang="ja">

      <mhlwsafetyreportid>JP-GILEAD-111</mhlwsafetyreportid>

      <mhlwsafetyreportversion>1</mhlwsafetyreportversion>

      <mhlwadmicsrinfoobtndatesource>

      <mhlwadmicsrreceiptdateformat>102</mhlwadmicsrreceiptdateformat>

      <mhlwadmicsrreceiptdate>20190102</mhlwadmicsrreceiptdate>

      </mhlwadmicsrinfoobtndatesource>

      etc. etc.

       

      But I need to now do the same with R3 XMLs, which are formatted like this:

      <?xml version="1.0" encoding="utf-8"?>

      <MCCI_IN200100UV01 ITSVersion="XML_1.0" xsi:schemaLocation="C:\Users\tmoir\Desktop\HL7ICSRSchema\MCCI_IN200100UV01.xsd" xmlns:mif="urn:hl7-org:v3/mif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:hl7-org:v3">

        <id extension="JT-20190201-0001" root="2.16.840.1.113883.3.989.2.1.3.22"/>

        <!--N.1.2: Batch Number-->

        <creationTime value="20190201112052.7350"/>

        <!--N.1.5: Date of Batch Transmission-->

        <responseModeCode code="D"/>

        <interactionId extension="MCCI_IN200100UV01" root="2.16.840.1.113883.1.6"/>

        <name code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.1" codeSystemVersion="2.0"/>

        <!-- N.1.1: Type of Messages in Batch-->

        <!--Message #1-->

        <PORR_IN049016UV>

        <id extension="JP-JT-BA201900214" root="2.16.840.1.113883.3.989.2.1.3.1"/>

        <!--N.2.r.1: Message Identifier-->

        <creationTime value="20190201112052.4510"/>

        <!--N.2.r.4: Date of Message Creation-->

        <interactionId extension="PORR_IN049016UV" root="2.16.840.1.113883.1.6"/>

        <processingCode code="P"/>

        <processingModeCode code="T"/>

        <acceptAckCode code="AL"/>

        <receiver typeCode="RCV">

        <device classCode="DEV" determinerCode="INSTANCE">

        <id extension="PMDA" root="2.16.840.1.113883.3.989.2.1.3.12"/>

        <!--N.2.r.3: Message Receiver Identifier-->

        </device>

        </receiver>

      etc. etc.

       

      How do I do this?

        • 1. Re: How to use XMLQuery to retrieve info from R3 XML
          3829237

          I tried the following:

           

          select x.xml_id, xd.*

          from jt_mig.XML_INSERT x,

          xmltable('MCCI_IN200100UV01'

            PASSING x.xml_data

            COLUMNS

              case_id varchar2(30) path 'PORR_IN49016UV/id/@extension'

          ) xd;

           

          And it did not return anything, which is odd, because it should return at least the xml_id, which is a sequence in the source table

          • 2. Re: How to use XMLQuery to retrieve info from R3 XML
            3829237

            Also these:

             

            select x.xml_id, xd.*

            from jt_mig.XML_INSERT x,

            xmltable('MCCI_IN200100UV01'

              PASSING x.xml_data

              COLUMNS

                case_id varchar2(30) path 'PORR_IN49016UV/id/@extension'

            ) xd;

             

            --<MCCI_IN200100UV01 ITSVersion="XML_1.0" xsi:schemaLocation="urn:hl7-org:v3 MCCI_IN200100UV01.xsd" xmlns:mif="urn:hl7-org:v3/mif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:hl7-org:v3">

             

            select x.*

            from xml_insert rt, xmltable(

              'MCCI_IN200100UV01/PORR_IN049016UV'

              passing rt.xml_data

              columns ext varchar2(30) path 'id/@extension',

                root varchar2(30) path 'id/@root',

                cTime varchar2(10) path 'creationTime/@value'

            ) x;

             

             

            SELECT XMLQUERY(

              'MCCI_IN200100UV01/id/@extension'

              PASSING x.xml_data

              RETURNING CONTENT

              ) AS CASE_NUM

            FROM xml_insert x;

            • 3. Re: How to use XMLQuery to retrieve info from R3 XML
              tsuji

              First of all, just to remind the notion of default namespace used in an xml document if you are not immediately recognizing it ...

              <MCCI_IN200100UV01 ITSVersion="XML_1.0" xsi:schemaLocation="C:\Users\tmoir\Desktop\HL7ICSRSchema\MCCI_IN200100U
                 V01.xsd" xmlns:mif="urn:hl7-org:v3/mif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns="urn:hl7-org:v3">
              

              The part xmlns="urn:hl7-org:v3" is the declaration of it.

               

              To properly take into account of it, you use the appropriate overload of the xmltable function (syntactically shown below). I would use a version where the namespaces non-null are always prefixed. Here the prefix is "ns" quite arbitrarily. In theory the prefix itself is completely decoupled from the source xml. No accord between them is necessary. With the query you used in the post #2, it would look like this.

              select x.xml_id, xd.*
              from jt_mig.XML_INSERT x,
              xmltable(
                xmlnamespaces('urn:hl7-org:v3' as "ns"),
                '/ns:MCCI_IN200100UV01'
                PASSING x.xml_data
                COLUMNS
                  case_id varchar2(30) path 'ns:PORR_IN49016UV/ns:id/@extension'
              ) xd;
              

              Similarly for your other attempts in post #3.

               

              Oracle xml db supports also the default namespace in xmltable which would look like this ...

              select x.xml_id, xd.*
              from jt_mig.XML_INSERT x,
              xmltable(
                xmlnamespaces(default 'urn:hl7-org:v3'),
                '/MCCI_IN200100UV01'
                PASSING x.xml_data
                COLUMNS
                  case_id varchar2(30) path 'PORR_IN49016UV/id/@extension'
              ) xd;
              

              but I would not too positively suggest using it as outside these kinds of applications, xpath (w3c) recommendations would not recognize default namespace in its use : hence, potentially confusing for those too committed in using it in that area of applications. Furthermore, attributes, if not in null namespace, would always need to be prefixed, and default namespace wouldn't be applicable to them. That may be just a detail for the moment.

              • 4. Re: How to use XMLQuery to retrieve info from R3 XML
                3829237

                So how do I query repeating groups?  There is an error in this one, but I think I am close

                 

                select x.xml_id "case_xref", xc.*, xd.*

                from jt_mig.XML_INSERT x, 

                xmltable( 

                  xmlnamespaces('urn:hl7-org:v3' as "xa"), 

                  '/xa:MCCI_IN200100UV01' 

                  PASSING x.xml_data

                  COLUMNS

                    batchmessagenumb varchar2(30) path 'id/@extension',

                    creationTime varchar2(30) path 'creationTime/@value',

                    messageidentifier varchar2(30) path 'PORR_IN049016UV/id/@extension',

                    receiver varchar2(30) path 'PORR_IN049016UV/receiver/device/id/@extension',

                    sender varchar2(30) path 'PORR_IN049016UV/sender/device/id/@extension',

                    PORR_IN049016UV xmltype path '.') xc,

                    xmltable('/xa:MCCI_IN200100UV01/PORR_IN049016UV/'

                  passing ('PORR_IN049016UV') columns

                    safetyreportid varchar2(30) path 'controlActProcess/subject/investigationEvent/id/@extension'

                    ) xd;

                • 5. Re: How to use XMLQuery to retrieve info from R3 XML
                  3829237

                  Also, what if the attribute is named the same as another attribute in the same child node?  Like two child nodes in the same node named "id", but are not repeating elements because their code values are different.

                   

                  <subject typeCode="SUBJ">

                          <investigationEvent classCode="INVSTG" moodCode="EVN">

                            <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.1" />

                            <!--C.1.1: Sender's (case) Safety Report Unique Identifier-->

                            <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.2" />

                            <!--C.1.8.1: Worldwide Unique Case Identification Number-->

                            <id root="2.16.840.1.113883.3.989.5.1.3.2.3.1" />

                            <!--@@@J2.1b: 識別番号(番号)-->

                            <code code="PAT_ADV_EVNT" codeSystem="2.16.840.1.113883.5.4" />

                            <text>2017/10/4の血液検査で高TG血症が増悪。

                  治療開始し2018/2/7に正常範囲まで改善した。

                   

                   

                  </text>

                  • 6. Re: How to use XMLQuery to retrieve info from R3 XML
                    tsuji

                    So how do I query repeating groups?

                    Ok, what groups? From the tentative sql, do you mean there are repeated either controlActProcess or subject/investigationEvent or id along with the chain in the xpath? If you can be more precise, may be we can answer more precisely.

                     

                    In any case, you have to review how to use the namespace prefix (xa, for instance) already. Its distribution over the xpath is completely off and in no way that sql would work.

                     

                    As a first response with more substance where you can learn something out of it, here is what it would look if id node in controlActProcess/subject/investigationEvent/id be appearing in multiple times...

                    select x.xml_id "case_xref", xc.*, xd.* 
                    from jt_mig.XML_INSERT x,  
                    xmltable(  
                      xmlnamespaces('urn:hl7-org:v3' as "xa"),  
                      '/xa:MCCI_IN200100UV01'  
                      PASSING x.xml_data
                      COLUMNS
                        batchmessagenumb varchar2(30) path 'xa:id/@extension',
                        creationTime varchar2(30) path 'xa:creationTime/@value',
                        messageidentifier varchar2(30) path 'xa:PORR_IN049016UV/xa:id/@extension',
                        receiver varchar2(30) path 'xa:PORR_IN049016UV/xa:receiver/xa:device/xa:id/@extension',
                        sender varchar2(30) path 'xa:PORR_IN049016UV/xa:sender/xa:device/xa:id/@extension',
                        ids xmltype path 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id'
                    ) xc,
                    xmltable('/xa:id'
                      xmlnamespaces('urn:hl7-org:v3' as "xa"),
                      PASSING xc.ids
                      COLUMNS
                        safetyreportid varchar2(30) path '@extension'
                    ) xd; 
                    

                    Look into the structure of how it is built. I may not know what exactly you've as xml, but if that particular id under xa:investigationEvent is multiple, this is how it can be done. Otherwise, you've to pose a fully consistent xml making clear all the nodes you have put into the xpaths.

                    • 7. Re: How to use XMLQuery to retrieve info from R3 XML
                      3829237

                      Here is the entire sample XML.

                       

                      Also, for some reason, your query throws "missing COLUMNS keyword after the namespace declaration in the xd block.

                      <?xml version="1.0" encoding="utf-8"?>

                      <MCCI_IN200100UV01 ITSVersion="XML_1.0" xsi:schemaLocation="urn:hl7-org:v3 MCCI_IN200100UV01.xsd" xmlns:mif="urn:hl7-org:v3/mif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:hl7-org:v3">

                        <id extension="JT-20190329-0005" root="2.16.840.1.113883.3.989.2.1.3.22" />

                        <!--N.1.2: Batch Number-->

                        <creationTime value="20190329111756.8290" />

                        <!--N.1.5: Date of Batch Transmission-->

                        <responseModeCode code="D" />

                        <interactionId extension="MCCI_IN200100UV01" root="2.16.840.1.113883.1.6" />

                        <name code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.1" codeSystemVersion="2.0" />

                        <!-- N.IN200100UV011.1: Type of Messages in Batch-->

                        <!--Message #1-->

                        <PORR_IN049016UV>

                          <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.1" />

                          <!--N.2.r.1: Message Identifier-->

                          <creationTime value="20190329111756.7980" />

                          <!--N.2.r.4: Date of Message Creation-->

                          <interactionId extension="PORR_IN049016UV" root="2.16.840.1.113883.1.6" />

                          <processingCode code="P" />

                          <processingModeCode code="T" />

                          <acceptAckCode code="AL" />

                          <receiver typeCode="RCV">

                            <device classCode="DEV" determinerCode="INSTANCE">

                              <id extension="PMDA" root="2.16.840.1.113883.3.989.2.1.3.12" />

                              <!--N.2.r.3: Message Receiver Identifier-->

                            </device>

                          </receiver>

                          <sender typeCode="SND">

                            <device classCode="DEV" determinerCode="INSTANCE">

                              <id extension="JT" root="2.16.840.1.113883.3.989.2.1.3.11" />

                              <!--N.2.r.2: Message Sender Identifier-->

                            </device>

                          </sender>

                          <controlActProcess moodCode="EVN" classCode="CACT">

                            <code code="PORR_TE049016UV" codeSystem="2.16.840.1.113883.1.18" />

                            <!--HL7 Trigger Event ID-->

                            <effectiveTime value="20190329111756.7980" />

                            <!--C.1.2: Date of Creation-->

                            <subject typeCode="SUBJ">

                              <investigationEvent classCode="INVSTG" moodCode="EVN">

                                <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.1" />

                                <!--C.1.1: Sender's (case) Safety Report Unique Identifier-->

                                <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.2" />

                                <!--C.1.8.1: Worldwide Unique Case Identification Number-->

                                <id root="2.16.840.1.113883.3.989.5.1.3.2.3.1" />

                                <!--@@@J2.1b: 識別番号(番号)-->

                                <code code="PAT_ADV_EVNT" codeSystem="2.16.840.1.113883.5.4" />

                                <text>2017/10/4の血液検査で高TG血症が増悪。

                      治療開始し2018/2/7に正常範囲まで改善した。

                       

                       

                      </text>

                                <!--H.1: Case Narrative Including Clinical Course, Therapeutic Measures, Outcome and Additional Relevant Information-->

                                <statusCode code="completed" />

                                <!--@@@J.2.7.1 完了、未完了区分-->

                                <effectiveTime>

                                  <low value="20190121" />

                                </effectiveTime>

                                <!--C.1.4: Date Report Was First Received from Source-->

                                <availabilityTime value="20190319" />

                                <!--C.1.5: Date of Most Recent Information for This Report-->

                                <component typeCode="COMP">

                                  <adverseEventAssessment classCode="INVSTG" moodCode="EVN">

                                    <subject1 typeCode="SBJ">

                                      <primaryRole classCode="INVSBJ">

                                        <player1 classCode="PSN" determinerCode="INSTANCE">

                                          <name nullFlavor="MSK" />

                                          <!--D.1: Patient (name or initials)-->

                                          <administrativeGenderCode code="1" codeSystem="1.0.5218" />

                                          <!--D.5 Sex-->

                                          <birthTime nullFlavor="MSK" />

                                          <!--D.2.1: Date of Birth-->

                                          <!--D.9.1: Date of Death-->

                                          <asIdentifiedEntity classCode="IDENT">

                                            <id root="2.16.840.1.113883.3.989.2.1.3.7" />

                                            <!--D.1.1.1: Patient Medical Record Number(s) and Source(s) of the Record Number (GP Medical Record Number)-->

                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" displayName="GP" />

                                          </asIdentifiedEntity>

                                          <asIdentifiedEntity classCode="IDENT">

                                            <id root="2.16.840.1.113883.3.989.2.1.3.8" />

                                            <!--D.1.1.2: Patient Medical Record Number(s) and Source(s) of the Record Number (Specialist Record Number)-->

                                            <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" displayName="Specialist" />

                                          </asIdentifiedEntity>

                                          <asIdentifiedEntity classCode="IDENT">

                                            <id nullFlavor="MSK" root="2.16.840.1.113883.3.989.2.1.3.9" />

                                            <!--D.1.1.3: Patient Medical Record Number(s) and Source(s) of the Record Number (Hospital Record Number)-->

                                            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" displayName="Hospital Record" />

                                          </asIdentifiedEntity>

                                          <asIdentifiedEntity classCode="IDENT">

                                            <id root="2.16.840.1.113883.3.989.2.1.3.10" />

                                            <!--D.1.1.4: Patient Medical Record Number(s) and Source(s) of the Record Number (Investigation Number)-->

                                            <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.4" codeSystemVersion="2.0" displayName="Investigation" />

                                          </asIdentifiedEntity>

                                          <role classCode="PRS">

                                            <code code="PRN" codeSystem="2.16.840.1.113883.5.111" />

                                            <associatedPerson classCode="PSN" determinerCode="INSTANCE">

                                              <!--D.10.1: Parent Identification-->

                                              <administrativeGenderCode codeSystem="1.0.5218" />

                                              <!--D.10.6: Sex of Parent-->

                                              <!--D.10.2.1: Date of Birth of Parent-->

                                            </associatedPerson>

                                            <subjectOf2 typeCode="SBJ">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="age" />

                                                <!--D.10.2.2a: Age of Parent (number)-->

                                                <!--D.10.2.2b: Age of Parent (unit)-->

                                              </observation>

                                            </subjectOf2>

                                            <subjectOf2 typeCode="SBJ">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="22" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="lastMenstrualPeriodDate" />

                                                <!--D.10.3: Last Menstrual Period Date of Parent-->

                                              </observation>

                                            </subjectOf2>

                                            <subjectOf2 typeCode="SBJ">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="7" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="bodyWeight" />

                                                <!--D.10.4: Body Weight (kg) of Parent-->

                                              </observation>

                                            </subjectOf2>

                                            <subjectOf2 typeCode="SBJ">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="17" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="height" />

                                                <!--D.10.5: Height (cm) of Parent-->

                                              </observation>

                                            </subjectOf2>

                                            <subjectOf2 typeCode="SBJ">

                                              <organizer classCode="CATEGORY" moodCode="EVN">

                                                <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="relevantMedicalHistoryAndConcurrentConditions" />

                                                <component typeCode="COMP">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="18" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="historyAndConcurrentConditionText" />

                                                    <!--D.10.7.2: Text for Relevant Medical History and Concurrent Conditions of Parent-->

                                                  </observation>

                                                </component>

                                              </organizer>

                                            </subjectOf2>

                                            <subjectOf2 typeCode="SBJ">

                                              <organizer classCode="CATEGORY" moodCode="EVN">

                                                <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="drugHistory" />

                                              </organizer>

                                            </subjectOf2>

                                          </role>

                                        </player1>

                                        <subjectOf1 typeCode="SBJ">

                                          <researchStudy classCode="CLNTRL" moodCode="EVN">

                                            <id extension="HRD1" root="2.16.840.1.113883.3.989.2.1.3.5" />

                                            <!--C.5.3: Sponsor Study Number-->

                                            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.8" codeSystemVersion="2.0" />

                                            <!--C.5.4: Study Type Where Reaction(s) / Event(s) Were Observed-->

                                            <title>HRD共同調査</title>

                                            <!--C.5.2: Study Name-->

                                          </researchStudy>

                                        </subjectOf1>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="age" />

                                            <value xsi:type="PQ" value="50" unit="a" />

                                            <!--D.2.2a: Age at Time of Onset of Reaction / Event (number) -->

                                            <!--D.2.2b: Age at Time of Onset of Reaction / Event (unit)-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="16" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="gestationPeriod" />

                                            <!--D.2.2.1a: Gestation Period When Reaction / Event Was Observed in the Foetus (number)-->

                                            <!--D.2.2.1b: Gestation Period When Reaction / Event Was Observed in the Foetus (unit)-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="ageGroup" />

                                            <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.9" />

                                            <!--D.2.3: Patient Age Group (as per reporter)-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="7" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="bodyWeight" />

                                            <!--D.3: Body Weight (kg)-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="17" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="height" />

                                            <!--D.4: Height (cm)-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="22" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="lastMenstrualPeriodDate" />

                                            <!--D.6: Last Menstrual Period Date-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <organizer classCode="CATEGORY" moodCode="EVN">

                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="relevantMedicalHistoryAndConcurrentConditions" />

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="10020161" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1" />

                                                <!--D.7.1.r.1a: MedDRA Version for Medical History-->

                                                <!--D.7.1.r.1b: Medical History (disease / surgical procedure / etc.) (MedDRA code)-->

                                                <effectiveTime xsi:type="IVL_TS" />

                                                <!--D.7.1.r.2: Start Date-->

                                                <!--D.7.1.r.4: End Date-->

                                                <outboundRelationship2 typeCode="COMP">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                                    <value xsi:type="ED">原疾患</value>

                                                    <!--D.7.1.r.5: Comments-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="EXPL">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="38" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="familyHistory" />

                                                    <!--D.7.1.r.6: Family History-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="REFR">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="13" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="continuing" />

                                                    <value xsi:type="BL" value="true" />

                                                    <!--D.7.1.r.3: Continuing-->

                                                  </observation>

                                                </inboundRelationship>

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="10018627" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1" />

                                                <!--D.7.1.r.1a: MedDRA Version for Medical History-->

                                                <!--D.7.1.r.1b: Medical History (disease / surgical procedure / etc.) (MedDRA code)-->

                                                <effectiveTime xsi:type="IVL_TS" />

                                                <!--D.7.1.r.2: Start Date-->

                                                <!--D.7.1.r.4: End Date-->

                                                <outboundRelationship2 typeCode="COMP">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                                    <value xsi:type="ED">合併症</value>

                                                    <!--D.7.1.r.5: Comments-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="EXPL">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="38" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="familyHistory" />

                                                    <!--D.7.1.r.6: Family History-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="REFR">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="13" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="continuing" />

                                                    <value xsi:type="BL" value="true" />

                                                    <!--D.7.1.r.3: Continuing-->

                                                  </observation>

                                                </inboundRelationship>

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="10067350" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1" />

                                                <!--D.7.1.r.1a: MedDRA Version for Medical History-->

                                                <!--D.7.1.r.1b: Medical History (disease / surgical procedure / etc.) (MedDRA code)-->

                                                <effectiveTime xsi:type="IVL_TS" />

                                                <!--D.7.1.r.2: Start Date-->

                                                <!--D.7.1.r.4: End Date-->

                                                <outboundRelationship2 typeCode="COMP">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                                    <value xsi:type="ED">合併症</value>

                                                    <!--D.7.1.r.5: Comments-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="EXPL">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="38" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="familyHistory" />

                                                    <!--D.7.1.r.6: Family History-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="REFR">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="13" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="continuing" />

                                                    <value xsi:type="BL" value="true" />

                                                    <!--D.7.1.r.3: Continuing-->

                                                  </observation>

                                                </inboundRelationship>

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="10020869" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1" />

                                                <!--D.7.1.r.1a: MedDRA Version for Medical History-->

                                                <!--D.7.1.r.1b: Medical History (disease / surgical procedure / etc.) (MedDRA code)-->

                                                <effectiveTime xsi:type="IVL_TS" />

                                                <!--D.7.1.r.2: Start Date-->

                                                <!--D.7.1.r.4: End Date-->

                                                <outboundRelationship2 typeCode="COMP">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                                    <value xsi:type="ED">合併症</value>

                                                    <!--D.7.1.r.5: Comments-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="EXPL">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="38" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="familyHistory" />

                                                    <!--D.7.1.r.6: Family History-->

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="REFR">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="13" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="continuing" />

                                                    <value xsi:type="BL" value="true" />

                                                    <!--D.7.1.r.3: Continuing-->

                                                  </observation>

                                                </inboundRelationship>

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="18" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="historyAndConcurrentConditionText" />

                                                <!--D.7.2: Text for Relevant Medical History and Concurrent Conditions (not including reaction / event) -->

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="11" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="concomitantTherapy" />

                                                <!--D.7.3:  Concomitant Therapies-->

                                              </observation>

                                            </component>

                                          </organizer>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <organizer classCode="CATEGORY" moodCode="EVN">

                                            <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="drugHistory" />

                                          </organizer>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <code code="5" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="autopsy" />

                                            <!--D.9.3  Was Autopsy Done?-->

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <observation classCode="OBS" moodCode="EVN">

                                            <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                            <code code="29" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="reaction" />

                                            <effectiveTime xsi:type="IVL_TS">

                                              <low value="20171004" />

                                              <!--E.i.4: Date of Start of Reaction / Event -->

                                            </effectiveTime>

                                            <value xsi:type="CE" code="10020869" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1">

                                              <originalText language="jpn">高TG血症の増悪</originalText>

                                            </value>

                                            <!--E.i.2.1a: MedDRA Version for Reaction / Event-->

                                            <!--E.i.2.1b: Reaction / Event (MedDRA code)-->

                                            <!--E.i.1.1a: Reaction / Event as Reported by the Primary Source in Native Language-->

                                            <!--E.i.1.1b: Reaction / Event as Reported by the Primary Source Language-->

                                            <location typeCode="LOC">

                                              <locatedEntity classCode="LOCE">

                                                <locatedPlace classCode="COUNTRY" determinerCode="INSTANCE">

                                                  <code code="JP" codeSystem="1.0.3166.1.2.2" />

                                                  <!--E.i.9 Identification of the Country Where the Reaction / Event Occurred-->

                                                </locatedPlace>

                                              </locatedEntity>

                                            </location>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="30" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="reactionForTranslation" />

                                                <!--E.i.1.2: ReacPCVtion / Event as Reported by the Primary Source for Translation-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="37" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="termHighlightedByReporter" />

                                                <value xsi:type="CE" code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.10" codeSystemVersion="2.0" />

                                                <!--E.i.3.1: Term Highlighted by the Reporter-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="34" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="resultsInDeath" />

                                                <value xsi:type="BL" nullFlavor="NI" />

                                                <!--E.i.3.2a: Results in Death-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="21" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="isLifeThreatening" />

                                                <value xsi:type="BL" nullFlavor="NI" />

                                                <!--E.i.3.2b: Life Threatening-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="33" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="requiresInpatientHospitalization" />

                                                <value xsi:type="BL" nullFlavor="NI" />

                                                <!--E.i.3.2c: Caused / Prolonged Hospitalisation-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="35" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="resultsInPersistentOrSignificantDisability" />

                                                <value xsi:type="BL" nullFlavor="NI" />

                                                <!--E.i.3.2d: Disabling / Incapacitating-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="12" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="congenitalAnomalyBirthDefect" />

                                                <value xsi:type="BL" nullFlavor="NI" />

                                                <!--E.i.3.2e: Congenital Anomaly / Birth Defect-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="26" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="otherMedicallyImportantCondition" />

                                                <value xsi:type="BL" value="true" />

                                                <!--E.i.3.2f: Other Medically Important Condition-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="27" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="outcome" />

                                                <value xsi:type="CE" code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.11" codeSystemVersion="2.0" />

                                                <!--E.i.7: Outcome of Reaction / Event at the Time of Last Observation-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="24" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="medicalConfirmationByHealthProfessional" />

                                                <!--E.i.8: Medical Confirmation by Healthcare Professional-->

                                              </observation>

                                            </outboundRelationship2>

                                            <outboundRelationship2 typeCode="PERT">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code code="11" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="未知・既知" />

                                                <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.8" />

                                                <!--@@@J2.14.i 未知・既知-->

                                              </observation>

                                            </outboundRelationship2>

                                          </observation>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <organizer classCode="CATEGORY" moodCode="EVN">

                                            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="testsAndProceduresRelevantToTheInvestigation" />

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code codeSystem="2.16.840.1.113883.6.163">

                                                  <originalText>TG</originalText>

                                                </code>

                                                <!--F.r.2.2a: MedDRA Version for Test Name-->

                                                <!--F.r.2.2b: Test Name (MedDRA code)-->

                                                <!--F.r.2.1: Test Name (free text)-->

                                                <effectiveTime value="20170712" />

                                                <!--F.r.1: Test Date-->

                                                <!-- F.r.3.4: Result Unstructured Data (free text) -->

                                                <value xsi:type="IVL_PQ">

                                                  <center value="283" unit="mg/dL" />

                                                  <!--F.r.3.2: Test Result (value / qualifier)-->

                                                  <!--F.r.3.3: Test Result (unit)-->

                                                </value>

                                                <interpretationCode codeSystem="2.16.840.1.113883.3.989.2.1.1.12" />

                                                <!--F.r.3.1: Test Result (code)-->

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code codeSystem="2.16.840.1.113883.6.163">

                                                  <originalText>TG</originalText>

                                                </code>

                                                <!--F.r.2.2a: MedDRA Version for Test Name-->

                                                <!--F.r.2.2b: Test Name (MedDRA code)-->

                                                <!--F.r.2.1: Test Name (free text)-->

                                                <effectiveTime value="20171004" />

                                                <!--F.r.1: Test Date-->

                                                <!-- F.r.3.4: Result Unstructured Data (free text) -->

                                                <value xsi:type="IVL_PQ">

                                                  <center value="834" unit="mg/dL" />

                                                  <!--F.r.3.2: Test Result (value / qualifier)-->

                                                  <!--F.r.3.3: Test Result (unit)-->

                                                </value>

                                                <interpretationCode codeSystem="2.16.840.1.113883.3.989.2.1.1.12" />

                                                <!--F.r.3.1: Test Result (code)-->

                                              </observation>

                                            </component>

                                            <component typeCode="COMP">

                                              <observation classCode="OBS" moodCode="EVN">

                                                <code codeSystem="2.16.840.1.113883.6.163">

                                                  <originalText>TG</originalText>

                                                </code>

                                                <!--F.r.2.2a: MedDRA Version for Test Name-->

                                                <!--F.r.2.2b: Test Name (MedDRA code)-->

                                                <!--F.r.2.1: Test Name (free text)-->

                                                <effectiveTime value="20180207" />

                                                <!--F.r.1: Test Date-->

                                                <!-- F.r.3.4: Result Unstructured Data (free text) -->

                                                <value xsi:type="IVL_PQ">

                                                  <center value="165" unit="mg/dL" />

                                                  <!--F.r.3.2: Test Result (value / qualifier)-->

                                                  <!--F.r.3.3: Test Result (unit)-->

                                                </value>

                                                <interpretationCode codeSystem="2.16.840.1.113883.3.989.2.1.1.12" />

                                                <!--F.r.3.1: Test Result (code)-->

                                              </observation>

                                            </component>

                                          </organizer>

                                        </subjectOf2>

                                        <subjectOf2 typeCode="SBJ">

                                          <organizer classCode="CATEGORY" moodCode="EVN">

                                            <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.20" codeSystemVersion="2.0" displayName="drugInformation" />

                                            <!--G.k Drug(s) Information (repeat as necessary)-->

                                            <component typeCode="COMP">

                                              <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                <id root="89ad2266-c003-4cf1-bc5c-f476163d21d7" />

                                                <!--drug id-->

                                                <consumable typeCode="CSM">

                                                  <instanceOfKind classCode="INST">

                                                    <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                      <!--G.k.2.1.1a: MPID Version Date / Number-->

                                                      <!--G.k.2.1.1b: Medicinal Product Identifier (MPID)-->

                                                      <name>625011101</name>

                                                      <!--G.k.2.2: Medicinal Product Name as Reported by the Primary Source -->

                                                      <asManufacturedProduct classCode="MANU">

                                                        <subjectOf typeCode="SBJ">

                                                          <approval classCode="CNTRCT" moodCode="EVN">

                                                            <id extension="22800AMX00717000" root="2.16.840.1.113883.3.989.2.1.3.4" />

                                                            <!--G.k.3.1: Authorisation / Application Number-->

                                                            <holder typeCode="HLD">

                                                              <role classCode="HLD">

                                                                <playingOrganization classCode="ORG" determinerCode="INSTANCE">

                                                                  <name>日本たばこ</name>

                                                                  <!--G.k.3.3: Name of Holder / Applicant-->

                                                                </playingOrganization>

                                                              </role>

                                                            </holder>

                                                            <author typeCode="AUT">

                                                              <territorialAuthority classCode="TERR">

                                                                <territory classCode="NAT" determinerCode="INSTANCE">

                                                                  <code code="JP" codeSystem="1.0.3166.1.2.2" />

                                                                  <!--G.k.3.2: Country of Authorisation / Application-->

                                                                </territory>

                                                              </territorialAuthority>

                                                            </author>

                                                            <pertinentInformation typeCode="PERT">

                                                              <policy classCode="POLICY" moodCode="EVN">

                                                                <code codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.4" />

                                                                <!--@@@J2.5.k: 一般用医薬品等のリスク区分等-->

                                                              </policy>

                                                            </pertinentInformation>

                                                          </approval>

                                                        </subjectOf>

                                                        <subjectOf typeCode="SBJ">

                                                          <characteristic classCode="OBS" moodCode="EVN">

                                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="新医薬品等の状況区分" />

                                                            <value xsi:type="CE" code="5" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.3" codeSystemVersion="1.1" />

                                                            <!--@@@J2.4.k: 新医薬品等の状況区分-->

                                                          </characteristic>

                                                        </subjectOf>

                                                      </asManufacturedProduct>

                                                      <ingredient classCode="ACTI">

                                                        <!--G.k.2.3.r.3a: Strength (number)-->

                                                        <!--G.k.2.3.r.3b: Strength (unit)-->

                                                        <ingredientSubstance classCode="MMAT" determinerCode="KIND">

                                                          <code codeSystem="TBD-Substance" />

                                                          <!--G.k.2.3.r.2a: Substance / Specified Substance TermID Version Date / Number-->

                                                          <!--G.k.2.3.r.2b: Substance / Specified Substance TermID-->

                                                          <name>6250111</name>

                                                          <!--G.k.2.3.r.1: Substance / Specified Substance Name-->

                                                        </ingredientSubstance>

                                                      </ingredient>

                                                    </kindOfProduct>

                                                    <subjectOf typeCode="SBJ">

                                                      <productEvent classCode="ACT" moodCode="EVN">

                                                        <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.18" codeSystemVersion="2.0" displayName="retailSupply" />

                                                        <performer typeCode="PRF">

                                                          <assignedEntity classCode="ASSIGNED">

                                                            <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                                              <addr>

                                                                <country>JP</country>

                                                                <!--G.k.2.4: Identification of the Country Where the Drug Was Obtained -->

                                                              </addr>

                                                            </representedOrganization>

                                                          </assignedEntity>

                                                        </performer>

                                                      </productEvent>

                                                    </subjectOf>

                                                    <subjectOf typeCode="SBJ">

                                                      <observationEvent classCode="OBS" moodCode="EVN">

                                                        <code code="2" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="一般用医薬品の入手経路" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.5" />

                                                        <!--@@@J2.6.k: 一般用医薬品の入手経路-->

                                                      </observationEvent>

                                                    </subjectOf>

                                                  </instanceOfKind>

                                                </consumable>

                                                <outboundRelationship2 typeCode="COMP">

                                                  <!--G.k.4.r: Dosage and Relevant Information (repeat as necessary)-->

                                                  <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                    <!--G.k.4.r.8: Dosage Text-->

                                                    <effectiveTime xsi:type="SXPR_TS">

                                                      <comp xsi:type="PIVL_TS">

                                                        <period value="1" unit="d" />

                                                        <!--G.k.4.r.2: Number of Units in the Interval-->

                                                        <!--G.k.4.r.3: Definition of the Time Interval Unit-->

                                                      </comp>

                                                      <comp xsi:type="IVL_TS" operator="A">

                                                        <low value="20170629" />

                                                        <!--G.k.4.r.4: Date and Time of Start of Drug-->

                                                      </comp>

                                                    </effectiveTime>

                                                    <routeCode code="048" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" codeSystemVersion="2.2" />

                                                    <!--G.k.4.r.10.2a: Route of Administration TermID Version Date / Number-->

                                                    <!--G.k.4.r.10.2b: Route of Administration TermID -->

                                                    <!--G.k.4.r.10.1: Route of Administration (free text)-->

                                                    <doseQuantity value="1" unit="{DF}" />

                                                    <!--G.k.4.r.1a Dose (number)-->

                                                    <!--G.k.4.r.1b: Dose (unit)-->

                                                    <consumable typeCode="CSM">

                                                      <instanceOfKind classCode="INST">

                                                        <productInstanceInstance classCode="MMAT" determinerCode="INSTANCE">

                                                          <!--G.k.4.r.7: Batch / Lot Number-->

                                                        </productInstanceInstance>

                                                        <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                          <formCode codeSystem="TBD-DoseForm">

                                                            <originalText>TAB</originalText>

                                                          </formCode>

                                                          <!--G.k.4.r.9.2a: Pharmaceutical Dose Form TermID Version Date / Number-->

                                                          <!--G.k.4.r.9.2b: Pharmaceutical Dose Form TermID-->

                                                          <!--G.k.4.r.9.1: Pharmaceutical Dose Form (free text)-->

                                                        </kindOfProduct>

                                                      </instanceOfKind>

                                                    </consumable>

                                                    <inboundRelationship typeCode="REFR">

                                                      <observation classCode="OBS" moodCode="EVN">

                                                        <code code="28" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="parentRouteOfAdministration" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" />

                                                        <!--G.k.4.r.11.2a: Parent Route of Administration TermID Version Date / Number-->

                                                        <!--G.k.4.r.11.2b: Parent Route of Administration TermID-->

                                                        <!--G.k.4.r.11.1: Parent Route of Administration (free text)-->

                                                      </observation>

                                                    </inboundRelationship>

                                                  </substanceAdministration>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="PERT">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="31" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="recurranceOfReaction" />

                                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.16" />

                                                    <!--G.k.9.i.4: Did Reaction Recur on Re-administration? Drug #, Reaction #-->

                                                    <outboundRelationship1 typeCode="REFR">

                                                      <actReference classCode="OBS" moodCode="EVN">

                                                        <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                                        <!--reaction id-->

                                                      </actReference>

                                                    </outboundRelationship1>

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="RSON">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="19" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="indication" />

                                                    <value xsi:type="CE" code="10020161" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1">

                                                      <originalText>HIV感染</originalText>

                                                    </value>

                                                    <!--G.k.7.r.2a: MedDRA Version for Indication-->

                                                    <!--G.k.7.r.2b: Indication (MedDRA code)-->

                                                    <!--G.k.7.r.1: Indication as Reported by the Primary Source-->

                                                    <performer typeCode="PRF">

                                                      <assignedEntity classCode="ASSIGNED">

                                                        <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.21" codeSystemVersion="2.0" displayName="sourceReporter" />

                                                      </assignedEntity>

                                                    </performer>

                                                  </observation>

                                                </inboundRelationship>

                                                <inboundRelationship typeCode="CAUS">

                                                  <act classCode="ACT" moodCode="EVN">

                                                    <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.15" codeSystemVersion="2.0" />

                                                    <!--G.k.8: Action(s) Taken with Drug-->

                                                  </act>

                                                </inboundRelationship>

                                              </substanceAdministration>

                                            </component>

                                            <component typeCode="COMP">

                                              <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                <id root="d5f95b3f-fc57-4ceb-a05d-a30d0203b5c9" />

                                                <!--drug id-->

                                                <consumable typeCode="CSM">

                                                  <instanceOfKind classCode="INST">

                                                    <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                      <!--G.k.2.1.1a: MPID Version Date / Number-->

                                                      <!--G.k.2.1.1b: Medicinal Product Identifier (MPID)-->

                                                      <name>625003801</name>

                                                      <!--G.k.2.2: Medicinal Product Name as Reported by the Primary Source -->

                                                      <asManufacturedProduct classCode="MANU">

                                                        <subjectOf typeCode="SBJ">

                                                          <approval classCode="CNTRCT" moodCode="EVN">

                                                            <id root="2.16.840.1.113883.3.989.2.1.3.4" />

                                                            <!--G.k.3.1: Authorisation / Application Number-->

                                                            <holder typeCode="HLD">

                                                              <role classCode="HLD">

                                                                <playingOrganization classCode="ORG" determinerCode="INSTANCE">

                                                                  <name>ヴィーブヘルスケア</name>

                                                                  <!--G.k.3.3: Name of Holder / Applicant-->

                                                                </playingOrganization>

                                                              </role>

                                                            </holder>

                                                            <author typeCode="AUT">

                                                              <territorialAuthority classCode="TERR">

                                                                <territory classCode="NAT" determinerCode="INSTANCE">

                                                                  <code codeSystem="1.0.3166.1.2.2" />

                                                                  <!--G.k.3.2: Country of Authorisation / Application-->

                                                                </territory>

                                                              </territorialAuthority>

                                                            </author>

                                                            <pertinentInformation typeCode="PERT">

                                                              <policy classCode="POLICY" moodCode="EVN">

                                                                <code codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.4" />

                                                                <!--@@@J2.5.k: 一般用医薬品等のリスク区分等-->

                                                              </policy>

                                                            </pertinentInformation>

                                                          </approval>

                                                        </subjectOf>

                                                        <subjectOf typeCode="SBJ">

                                                          <characteristic classCode="OBS" moodCode="EVN">

                                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="新医薬品等の状況区分" />

                                                            <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.3" />

                                                            <!--@@@J2.4.k: 新医薬品等の状況区分-->

                                                          </characteristic>

                                                        </subjectOf>

                                                      </asManufacturedProduct>

                                                      <ingredient classCode="ACTI">

                                                        <!--G.k.2.3.r.3a: Strength (number)-->

                                                        <!--G.k.2.3.r.3b: Strength (unit)-->

                                                        <ingredientSubstance classCode="MMAT" determinerCode="KIND">

                                                          <code codeSystem="TBD-Substance" />

                                                          <!--G.k.2.3.r.2a: Substance / Specified Substance TermID Version Date / Number-->

                                                          <!--G.k.2.3.r.2b: Substance / Specified Substance TermID-->

                                                          <name>6250038</name>

                                                          <!--G.k.2.3.r.1: Substance / Specified Substance Name-->

                                                        </ingredientSubstance>

                                                      </ingredient>

                                                    </kindOfProduct>

                                                    <subjectOf typeCode="SBJ">

                                                      <productEvent classCode="ACT" moodCode="EVN">

                                                        <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.18" codeSystemVersion="2.0" displayName="retailSupply" />

                                                        <performer typeCode="PRF">

                                                          <assignedEntity classCode="ASSIGNED">

                                                            <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                                              <addr>

                                                                <country>JP</country>

                                                                <!--G.k.2.4: Identification of the Country Where the Drug Was Obtained -->

                                                              </addr>

                                                            </representedOrganization>

                                                          </assignedEntity>

                                                        </performer>

                                                      </productEvent>

                                                    </subjectOf>

                                                    <subjectOf typeCode="SBJ">

                                                      <observationEvent classCode="OBS" moodCode="EVN">

                                                        <code code="2" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="一般用医薬品の入手経路" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.5" />

                                                        <!--@@@J2.6.k: 一般用医薬品の入手経路-->

                                                      </observationEvent>

                                                    </subjectOf>

                                                  </instanceOfKind>

                                                </consumable>

                                                <outboundRelationship2 typeCode="COMP">

                                                  <!--G.k.4.r: Dosage and Relevant Information (repeat as necessary)-->

                                                  <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                    <!--G.k.4.r.8: Dosage Text-->

                                                    <effectiveTime xsi:type="SXPR_TS">

                                                      <comp xsi:type="PIVL_TS">

                                                        <period value="1" unit="d" />

                                                        <!--G.k.4.r.2: Number of Units in the Interval-->

                                                        <!--G.k.4.r.3: Definition of the Time Interval Unit-->

                                                      </comp>

                                                      <comp xsi:type="IVL_TS" operator="A">

                                                        <low value="20170629" />

                                                        <!--G.k.4.r.4: Date and Time of Start of Drug-->

                                                      </comp>

                                                    </effectiveTime>

                                                    <routeCode code="048" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" codeSystemVersion="2.2" />

                                                    <!--G.k.4.r.10.2a: Route of Administration TermID Version Date / Number-->

                                                    <!--G.k.4.r.10.2b: Route of Administration TermID -->

                                                    <!--G.k.4.r.10.1: Route of Administration (free text)-->

                                                    <doseQuantity value="50" unit="mg" />

                                                    <!--G.k.4.r.1a Dose (number)-->

                                                    <!--G.k.4.r.1b: Dose (unit)-->

                                                    <consumable typeCode="CSM">

                                                      <instanceOfKind classCode="INST">

                                                        <productInstanceInstance classCode="MMAT" determinerCode="INSTANCE">

                                                          <!--G.k.4.r.7: Batch / Lot Number-->

                                                        </productInstanceInstance>

                                                        <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                          <formCode codeSystem="TBD-DoseForm">

                                                            <originalText>TAB</originalText>

                                                          </formCode>

                                                          <!--G.k.4.r.9.2a: Pharmaceutical Dose Form TermID Version Date / Number-->

                                                          <!--G.k.4.r.9.2b: Pharmaceutical Dose Form TermID-->

                                                          <!--G.k.4.r.9.1: Pharmaceutical Dose Form (free text)-->

                                                        </kindOfProduct>

                                                      </instanceOfKind>

                                                    </consumable>

                                                    <inboundRelationship typeCode="REFR">

                                                      <observation classCode="OBS" moodCode="EVN">

                                                        <code code="28" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="parentRouteOfAdministration" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" />

                                                        <!--G.k.4.r.11.2a: Parent Route of Administration TermID Version Date / Number-->

                                                        <!--G.k.4.r.11.2b: Parent Route of Administration TermID-->

                                                        <!--G.k.4.r.11.1: Parent Route of Administration (free text)-->

                                                      </observation>

                                                    </inboundRelationship>

                                                  </substanceAdministration>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="PERT">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="31" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="recurranceOfReaction" />

                                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.16" />

                                                    <!--G.k.9.i.4: Did Reaction Recur on Re-administration? Drug #, Reaction #-->

                                                    <outboundRelationship1 typeCode="REFR">

                                                      <actReference classCode="OBS" moodCode="EVN">

                                                        <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                                        <!--reaction id-->

                                                      </actReference>

                                                    </outboundRelationship1>

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="RSON">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="19" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="indication" />

                                                    <value xsi:type="CE" code="10020161" codeSystem="2.16.840.1.113883.6.163" codeSystemVersion="21.1">

                                                      <originalText>HIV感染</originalText>

                                                    </value>

                                                    <!--G.k.7.r.2a: MedDRA Version for Indication-->

                                                    <!--G.k.7.r.2b: Indication (MedDRA code)-->

                                                    <!--G.k.7.r.1: Indication as Reported by the Primary Source-->

                                                    <performer typeCode="PRF">

                                                      <assignedEntity classCode="ASSIGNED">

                                                        <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.21" codeSystemVersion="2.0" displayName="sourceReporter" />

                                                      </assignedEntity>

                                                    </performer>

                                                  </observation>

                                                </inboundRelationship>

                                                <inboundRelationship typeCode="CAUS">

                                                  <act classCode="ACT" moodCode="EVN">

                                                    <code code="4" codeSystem="2.16.840.1.113883.3.989.2.1.1.15" codeSystemVersion="2.0" />

                                                    <!--G.k.8: Action(s) Taken with Drug-->

                                                  </act>

                                                </inboundRelationship>

                                              </substanceAdministration>

                                            </component>

                                            <component typeCode="COMP">

                                              <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                <id root="6e72f489-7017-48a6-a84f-d829ea984e79" />

                                                <!--drug id-->

                                                <consumable typeCode="CSM">

                                                  <instanceOfKind classCode="INST">

                                                    <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                      <!--G.k.2.1.1a: MPID Version Date / Number-->

                                                      <!--G.k.2.1.1b: Medicinal Product Identifier (MPID)-->

                                                      <name>394900301</name>

                                                      <!--G.k.2.2: Medicinal Product Name as Reported by the Primary Source -->

                                                      <asManufacturedProduct classCode="MANU">

                                                        <subjectOf typeCode="SBJ">

                                                          <approval classCode="CNTRCT" moodCode="EVN">

                                                            <id root="2.16.840.1.113883.3.989.2.1.3.4" />

                                                            <!--G.k.3.1: Authorisation / Application Number-->

                                                            <holder typeCode="HLD">

                                                              <role classCode="HLD">

                                                                <playingOrganization classCode="ORG" determinerCode="INSTANCE">

                                                                  <name>帝人ファーマ</name>

                                                                  <!--G.k.3.3: Name of Holder / Applicant-->

                                                                </playingOrganization>

                                                              </role>

                                                            </holder>

                                                            <author typeCode="AUT">

                                                              <territorialAuthority classCode="TERR">

                                                                <territory classCode="NAT" determinerCode="INSTANCE">

                                                                  <code codeSystem="1.0.3166.1.2.2" />

                                                                  <!--G.k.3.2: Country of Authorisation / Application-->

                                                                </territory>

                                                              </territorialAuthority>

                                                            </author>

                                                            <pertinentInformation typeCode="PERT">

                                                              <policy classCode="POLICY" moodCode="EVN">

                                                                <code codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.4" />

                                                                <!--@@@J2.5.k: 一般用医薬品等のリスク区分等-->

                                                              </policy>

                                                            </pertinentInformation>

                                                          </approval>

                                                        </subjectOf>

                                                        <subjectOf typeCode="SBJ">

                                                          <characteristic classCode="OBS" moodCode="EVN">

                                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="新医薬品等の状況区分" />

                                                            <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.3" />

                                                            <!--@@@J2.4.k: 新医薬品等の状況区分-->

                                                          </characteristic>

                                                        </subjectOf>

                                                      </asManufacturedProduct>

                                                      <ingredient classCode="ACTI">

                                                        <!--G.k.2.3.r.3a: Strength (number)-->

                                                        <!--G.k.2.3.r.3b: Strength (unit)-->

                                                        <ingredientSubstance classCode="MMAT" determinerCode="KIND">

                                                          <code codeSystem="TBD-Substance" />

                                                          <!--G.k.2.3.r.2a: Substance / Specified Substance TermID Version Date / Number-->

                                                          <!--G.k.2.3.r.2b: Substance / Specified Substance TermID-->

                                                          <name>3949003</name>

                                                          <!--G.k.2.3.r.1: Substance / Specified Substance Name-->

                                                        </ingredientSubstance>

                                                      </ingredient>

                                                    </kindOfProduct>

                                                    <subjectOf typeCode="SBJ">

                                                      <productEvent classCode="ACT" moodCode="EVN">

                                                        <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.18" codeSystemVersion="2.0" displayName="retailSupply" />

                                                        <performer typeCode="PRF">

                                                          <assignedEntity classCode="ASSIGNED">

                                                            <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                                              <addr>

                                                                <country>JP</country>

                                                                <!--G.k.2.4: Identification of the Country Where the Drug Was Obtained -->

                                                              </addr>

                                                            </representedOrganization>

                                                          </assignedEntity>

                                                        </performer>

                                                      </productEvent>

                                                    </subjectOf>

                                                    <subjectOf typeCode="SBJ">

                                                      <observationEvent classCode="OBS" moodCode="EVN">

                                                        <code code="2" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="一般用医薬品の入手経路" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.5" />

                                                        <!--@@@J2.6.k: 一般用医薬品の入手経路-->

                                                      </observationEvent>

                                                    </subjectOf>

                                                  </instanceOfKind>

                                                </consumable>

                                                <outboundRelationship2 typeCode="COMP">

                                                  <!--G.k.4.r: Dosage and Relevant Information (repeat as necessary)-->

                                                  <substanceAdministration classCode="SBADM" moodCode="EVN">

                                                    <!--G.k.4.r.8: Dosage Text-->

                                                    <effectiveTime xsi:type="IVL_TS">

                                                      <low value="20170629" />

                                                      <!--G.k.4.r.4: Date and Time of Start of Drug-->

                                                    </effectiveTime>

                                                    <routeCode code="048" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" codeSystemVersion="2.2" />

                                                    <!--G.k.4.r.10.2a: Route of Administration TermID Version Date / Number-->

                                                    <!--G.k.4.r.10.2b: Route of Administration TermID -->

                                                    <!--G.k.4.r.10.1: Route of Administration (free text)-->

                                                    <!--G.k.4.r.1a Dose (number)-->

                                                    <!--G.k.4.r.1b: Dose (unit)-->

                                                    <consumable typeCode="CSM">

                                                      <instanceOfKind classCode="INST">

                                                        <productInstanceInstance classCode="MMAT" determinerCode="INSTANCE">

                                                          <!--G.k.4.r.7: Batch / Lot Number-->

                                                        </productInstanceInstance>

                                                        <kindOfProduct classCode="MMAT" determinerCode="KIND">

                                                          <formCode codeSystem="TBD-DoseForm">

                                                            <originalText>TAB</originalText>

                                                          </formCode>

                                                          <!--G.k.4.r.9.2a: Pharmaceutical Dose Form TermID Version Date / Number-->

                                                          <!--G.k.4.r.9.2b: Pharmaceutical Dose Form TermID-->

                                                          <!--G.k.4.r.9.1: Pharmaceutical Dose Form (free text)-->

                                                        </kindOfProduct>

                                                      </instanceOfKind>

                                                    </consumable>

                                                    <inboundRelationship typeCode="REFR">

                                                      <observation classCode="OBS" moodCode="EVN">

                                                        <code code="28" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="parentRouteOfAdministration" />

                                                        <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.14" />

                                                        <!--G.k.4.r.11.2a: Parent Route of Administration TermID Version Date / Number-->

                                                        <!--G.k.4.r.11.2b: Parent Route of Administration TermID-->

                                                        <!--G.k.4.r.11.1: Parent Route of Administration (free text)-->

                                                      </observation>

                                                    </inboundRelationship>

                                                  </substanceAdministration>

                                                </outboundRelationship2>

                                                <outboundRelationship2 typeCode="PERT">

                                                  <observation classCode="OBS" moodCode="EVN">

                                                    <code code="31" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="recurranceOfReaction" />

                                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.16" />

                                                    <!--G.k.9.i.4: Did Reaction Recur on Re-administration? Drug #, Reaction #-->

                                                    <outboundRelationship1 typeCode="REFR">

                                                      <actReference classCode="OBS" moodCode="EVN">

                                                        <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                                        <!--reaction id-->

                                                      </actReference>

                                                    </outboundRelationship1>

                                                  </observation>

                                                </outboundRelationship2>

                                                <inboundRelationship typeCode="CAUS">

                                                  <act classCode="ACT" moodCode="EVN">

                                                    <code codeSystem="2.16.840.1.113883.3.989.2.1.1.15" />

                                                    <!--G.k.8: Action(s) Taken with Drug-->

                                                  </act>

                                                </inboundRelationship>

                                              </substanceAdministration>

                                            </component>

                                          </organizer>

                                        </subjectOf2>

                                      </primaryRole>

                                    </subject1>

                                    <component typeCode="COMP">

                                      <causalityAssessment classCode="OBS" moodCode="EVN">

                                        <code code="20" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="interventionCharacterization" />

                                        <value xsi:type="CE" code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.13" codeSystemVersion="2.0" />

                                        <!--G.k.1: Characterisation of Drug Role Drug-->

                                        <subject2 typeCode="SUBJ">

                                          <productUseReference classCode="SBADM" moodCode="EVN">

                                            <id root="89ad2266-c003-4cf1-bc5c-f476163d21d7" />

                                          </productUseReference>

                                        </subject2>

                                      </causalityAssessment>

                                    </component>

                                    <component typeCode="COMP">

                                      <causalityAssessment classCode="OBS" moodCode="EVN">

                                        <code code="20" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="interventionCharacterization" />

                                        <value xsi:type="CE" code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.13" codeSystemVersion="2.0" />

                                        <!--G.k.1: Characterisation of Drug Role Drug-->

                                        <subject2 typeCode="SUBJ">

                                          <productUseReference classCode="SBADM" moodCode="EVN">

                                            <id root="d5f95b3f-fc57-4ceb-a05d-a30d0203b5c9" />

                                          </productUseReference>

                                        </subject2>

                                      </causalityAssessment>

                                    </component>

                                    <component typeCode="COMP">

                                      <causalityAssessment classCode="OBS" moodCode="EVN">

                                        <code code="20" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="interventionCharacterization" />

                                        <value xsi:type="CE" code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.13" codeSystemVersion="2.0" />

                                        <!--G.k.1: Characterisation of Drug Role Drug-->

                                        <subject2 typeCode="SUBJ">

                                          <productUseReference classCode="SBADM" moodCode="EVN">

                                            <id root="6e72f489-7017-48a6-a84f-d829ea984e79" />

                                          </productUseReference>

                                        </subject2>

                                      </causalityAssessment>

                                    </component>

                                    <component typeCode="COMP">

                                      <causalityAssessment classCode="OBS" moodCode="EVN">

                                        <code code="39" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="causality" />

                                        <value xsi:type="ST">否定できない</value>

                                        <!--G.k.9.i.2.r.3: Result of Assessment Drug-->

                                        <methodCode>

                                          <originalText>全般的な観察評価</originalText>

                                          <!--G.k.9.i.2.r.2: Method of Assessment Drug-->

                                        </methodCode>

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <code>

                                              <originalText>報告者</originalText>

                                              <!--G.k.9.i.2.r.1: Source of Assessment Drug-->

                                            </code>

                                          </assignedEntity>

                                        </author>

                                        <subject1 typeCode="SUBJ">

                                          <adverseEffectReference classCode="OBS" moodCode="EVN">

                                            <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                          </adverseEffectReference>

                                        </subject1>

                                        <subject2 typeCode="SUBJ">

                                          <productUseReference classCode="SBADM" moodCode="EVN">

                                            <id root="89ad2266-c003-4cf1-bc5c-f476163d21d7" />

                                          </productUseReference>

                                        </subject2>

                                      </causalityAssessment>

                                    </component>

                                    <component typeCode="COMP">

                                      <causalityAssessment classCode="OBS" moodCode="EVN">

                                        <code code="39" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="causality" />

                                        <value xsi:type="ST">否定できない</value>

                                        <!--G.k.9.i.2.r.3: Result of Assessment Drug-->

                                        <methodCode>

                                          <!--G.k.9.i.2.r.2: Method of Assessment Drug-->

                                        </methodCode>

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <code>

                                              <originalText>企業</originalText>

                                              <!--G.k.9.i.2.r.1: Source of Assessment Drug-->

                                            </code>

                                          </assignedEntity>

                                        </author>

                                        <subject1 typeCode="SUBJ">

                                          <adverseEffectReference classCode="OBS" moodCode="EVN">

                                            <id root="5a0d595d-455b-4301-b224-51c62ba32e05" />

                                          </adverseEffectReference>

                                        </subject1>

                                        <subject2 typeCode="SUBJ">

                                          <productUseReference classCode="SBADM" moodCode="EVN">

                                            <id root="89ad2266-c003-4cf1-bc5c-f476163d21d7" />

                                          </productUseReference>

                                        </subject2>

                                      </causalityAssessment>

                                    </component>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                        <value xsi:type="ED">高TG血症の増悪 HIV/AIDSとの関連性 否定できない

                      高TG血症の増悪 テビケイとの関連性 否定できない</value>

                                        <!--H.2: Reporter's Comments-->

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.21" codeSystemVersion="2.0" displayName="sourceReporter" />

                                          </assignedEntity>

                                        </author>

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="10" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="comment" />

                                        <value xsi:type="ED">高TG血症の増悪は、本剤投与後に発現していることより報告医の意見の如く、関連性は否定できないと考える。</value>

                                        <!--H.4: Sender's Comments-->

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.21" codeSystemVersion="2.0" displayName="sender" />

                                          </assignedEntity>

                                        </author>

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="3" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="未完了に対するコメント" />

                                        <!--@@@J2.7.2: 未完了に対するコメント-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="4" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="感染症の遡及調査" />

                                        <!--@@@J2.9: 感染症の遡及調査-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="5" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="今後の対応" />

                                        <value xsi:type="ED">今後も類似の副作用情報収集に努める。</value>

                                        <!--@@@J2.10: 今後の対応-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="6" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="その他参考事項等" />

                                        <value xsi:type="ED">1.使用上の注意記載状況

                      高TG血症の増悪:(国内)記載なし (CCDS)記載なし

                      2.累積報告件数

                      高TG血症の増悪:(国内)1件(今回の報告を含む)/(外国)0件</value>

                                        <!--@@@J2.11: その他参考事項等-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="12" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="報告内容の要点" />

                                        <!--@@@J2.16: 報告内容の要点-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="13" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="備考1" />

                                        <!--@@@J2.19: 備考1-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="14" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="備考2" />

                                        <!--@@@J2.20: 備考2-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="15" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="備考3" />

                                        <!--@@@J2.21: 備考3-->

                                      </observationEvent>

                                    </component1>

                                    <component1 typeCode="COMP">

                                      <observationEvent classCode="OBS" moodCode="EVN">

                                        <code code="16" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.10" codeSystemVersion="1.1" displayName="備考4" />

                                        <!--@@@J2.22: 備考4-->

                                      </observationEvent>

                                    </component1>

                                  </adverseEventAssessment>

                                </component>

                                <component typeCode="COMP">

                                  <observationEvent classCode="OBS" moodCode="EVN">

                                    <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="additionalDocumentsAvailable" />

                                    <value xsi:type="BL" value="false" />

                                    <!--C.1.6.1: Are Additional Documents Available?-->

                                  </observationEvent>

                                </component>

                                <component typeCode="COMP">

                                  <observationEvent classCode="OBS" moodCode="EVN">

                                    <code code="23" codeSystem="2.16.840.1.113883.3.989.2.1.1.19" codeSystemVersion="2.0" displayName="localCriteriaForExpedited" />

                                    <value xsi:type="BL" value="true" />

                                    <!--C.1.7: Does This Case Fulfil the Local Criteria for an Expedited Report?-->

                                  </observationEvent>

                                </component>

                                <outboundRelationship typeCode="SPRT">

                                  <relatedInvestigation classCode="INVSTG" moodCode="EVN">

                                    <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.22" codeSystemVersion="2.0" displayName="initialReport" />

                                    <subjectOf2 typeCode="SUBJ">

                                      <controlActEvent classCode="CACT" moodCode="EVN">

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.3" codeSystemVersion="2.0" />

                                            <!--C.1.8.2: First Sender of This Case-->

                                          </assignedEntity>

                                        </author>

                                      </controlActEvent>

                                    </subjectOf2>

                                  </relatedInvestigation>

                                </outboundRelationship>

                                <outboundRelationship typeCode="SPRT">

                                  <relatedInvestigation classCode="INVSTG" moodCode="EVN">

                                    <code nullFlavor="NA" />

                                    <subjectOf2 typeCode="SUBJ">

                                      <controlActEvent classCode="CACT" moodCode="EVN">

                                        <id extension="JP-JT-EVA201900225GILEAD-001" root="2.16.840.1.113883.3.989.2.1.3.2" />

                                        <!--C.1.10.r: Identification Number of the Report Which Is Linked to this Report (repeat as necessary)-->

                                      </controlActEvent>

                                    </subjectOf2>

                                  </relatedInvestigation>

                                </outboundRelationship>

                                <outboundRelationship typeCode="SPRT">

                                  <priorityNumber value="1" />

                                  <!--C.2.r.5: Primary Source for Regulatory Purposes-->

                                  <relatedInvestigation classCode="INVSTG" moodCode="EVN">

                                    <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.22" codeSystemVersion="2.0" displayName="sourceReport" />

                                    <subjectOf2 typeCode="SUBJ">

                                      <controlActEvent classCode="CACT" moodCode="EVN">

                                        <author typeCode="AUT">

                                          <assignedEntity classCode="ASSIGNED">

                                            <addr>

                                              <!--C.2.r.2.3: Reporter's Street-->

                                              <!--C.2.r.2.4: Reporter's City-->

                                              <!--C.2.r.2.5: Reporter's State or Province-->

                                              <!--C.2.r.2.6: Reporter's Postcode-->

                                            </addr>

                                            <telecom value="tel:" />

                                            <!--C.2.r.2.7: Reporter's Telephone-->

                                            <assignedPerson classCode="PSN" determinerCode="INSTANCE">

                                              <name>

                                                <given />

                                                <!--C.2.r.1.2: Reporter's Given Name-->

                                                <given />

                                                <!--C.2.r.1.3: Reporter's Middle Name-->

                                                <family />

                                                <!--C.2.r.1.4: Reporter's Family Name-->

                                              </name>

                                              <asQualifiedEntity classCode="QUAL">

                                                <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.6" codeSystemVersion="2.0" />

                                                <!--C.2.r.4: Qualification-->

                                              </asQualifiedEntity>

                                              <asLocatedEntity classCode="LOCE">

                                                <location classCode="COUNTRY" determinerCode="INSTANCE">

                                                  <code code="JP" codeSystem="1.0.3166.1.2.2" />

                                                  <!--C.2.r.3: Reporter's Country Code-->

                                                </location>

                                              </asLocatedEntity>

                                            </assignedPerson>

                                            <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                              <!--C.2.r.2.2: Reporter's Department-->

                                              <assignedEntity classCode="ASSIGNED">

                                                <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                                  <!--C.2.r.2.1: Reporter's Organisation-->

                                                </representedOrganization>

                                              </assignedEntity>

                                            </representedOrganization>

                                          </assignedEntity>

                                        </author>

                                      </controlActEvent>

                                    </subjectOf2>

                                  </relatedInvestigation>

                                </outboundRelationship>

                                <subjectOf1 typeCode="SUBJ">

                                  <controlActEvent classCode="CACT" moodCode="EVN">

                                    <author typeCode="AUT">

                                      <assignedEntity classCode="ASSIGNED">

                                        <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.7" codeSystemVersion="2.0" />

                                        <!--C.3.1: Sender Type-->

                                        <addr>

                                          <streetAddressLine>虎ノ門二丁目2番1号</streetAddressLine>

                                          <!--C.3.4.1: Sender's Street Address-->

                                          <city>港区</city>

                                          <!--C.3.4.2: Sender's City-->

                                          <state>東京都</state>

                                          <!--C.3.4.3: Sender's State or Province-->

                                          <postalCode>105-8422</postalCode>

                                          <!--C.3.4.4: Sender's Postcode-->

                                        </addr>

                                        <telecom value="tel:" />

                                        <!--C.3.4.6: Sender's Telephone-->

                                        <telecom value="fax:" />

                                        <!--C.3.4.7: Sender's Fax-->

                                        <telecom value="mailto:" />

                                        <!--C.3.4.8: Sender's E-mail Address-->

                                        <assignedPerson classCode="PSN" determinerCode="INSTANCE">

                                          <name>

                                            <prefix>代表取締役社長</prefix>

                                            <!--C.3.3.2: Sender's Title-->

                                            <given>正道</given>

                                            <!--C.3.3.3: Sender's Given Name-->

                                            <given />

                                            <!--C.3.3.4: Sender's Middle Name-->

                                            <family>寺畠</family>

                                            <!--C.3.3.5: Sender's Family Name-->

                                          </name>

                                          <asLocatedEntity classCode="LOCE">

                                            <location classCode="COUNTRY" determinerCode="INSTANCE">

                                              <code code="JP" codeSystem="1.0.3166.1.2.2" />

                                              <!--C.3.4.5: Sender's Country Code-->

                                            </location>

                                          </asLocatedEntity>

                                        </assignedPerson>

                                        <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                          <!--C.3.3.1: Sender's Department-->

                                          <assignedEntity classCode="ASSIGNED">

                                            <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                              <name>JT</name>

                                              <!--C.3.2: Sender's Organisation-->

                                            </representedOrganization>

                                          </assignedEntity>

                                        </representedOrganization>

                                      </assignedEntity>

                                    </author>

                                    <primaryInformationRecipient typeCode="PRCP">

                                      <assignedEntity classCode="ASSIGNED">

                                        <assignedPerson classCode="PSN" determinerCode="INSTANCE">

                                          <name>

                                            <prefix>理事長</prefix>

                                            <!--@@@J2.18.2: 受信者肩書き-->

                                            <family>近藤</family>

                                            <!--@@@J2.18.3: 受信者姓-->

                                            <given>達也</given>

                                            <!--@@@J2.18.4: 受信者名-->

                                          </name>

                                        </assignedPerson>

                                        <representedOrganization classCode="ORG" determinerCode="INSTANCE">

                                          <name>独立行政法人医薬品医療機器総合機構</name>

                                          <!--@@@J2.18.1: 受信者組織名-->

                                        </representedOrganization>

                                      </assignedEntity>

                                    </primaryInformationRecipient>

                                  </controlActEvent>

                                </subjectOf1>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="1" codeSystem="2.16.840.1.113883.3.989.2.1.1.23" codeSystemVersion="2.0" displayName="ichReportType" />

                                    <value xsi:type="CE" code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.2" codeSystemVersion="2.0" />

                                    <!--C.1.3 Type of Report-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="2" codeSystem="2.16.840.1.113883.3.989.2.1.1.23" codeSystemVersion="2.0" displayName="otherCaseIds" />

                                    <value xsi:type="BL" nullFlavor="NI" />

                                    <!--C.1.9.1 Other Case Identifiers in Previous Transmissions-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="3" codeSystem="2.16.840.1.113883.3.989.2.1.1.23" codeSystemVersion="2.0" displayName="nullificationAmendmentCode" />

                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.2.1.1.5" />

                                    <!--C.1.11.1: Report Nullification / Amendment-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="1" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.12" codeSystemVersion="1.1" displayName="識別番号(報告分類)" />

                                    <value xsi:type="CE" code="AB" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.1" codeSystemVersion="1.2" />

                                    <!--@@@J2.1a: 識別番号(報告分類)-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="2" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.12" codeSystemVersion="1.1" displayName="報告起算日" />

                                    <value xsi:type="TS" value="20190319" />

                                    <!--@@@J2.2.1: 報告起算日-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="3" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.12" codeSystemVersion="1.1" displayName="報告起算日に関するコメント" />

                                    <value xsi:type="ED">症例に関する情報は2019/01/21に入手したが、報告対象事象「高TG血症の増悪」が重篤であるとの情報を入手した日が2019/03/19であるため、後者を起算日とする。</value>

                                    <!--@@@J2.2.2: 報告起算日に関するコメント-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="4" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.12" codeSystemVersion="1.1" displayName="即時報告フラグ" />

                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.2" />

                                    <!--@@@J2.3: 即時報告フラグ-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                                <subjectOf2 typeCode="SUBJ">

                                  <investigationCharacteristic classCode="OBS" moodCode="EVN">

                                    <code code="5" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.12" codeSystemVersion="1.1" displayName="報告対象外フラグ" />

                                    <value xsi:type="CE" codeSystem="2.16.840.1.113883.3.989.5.1.3.2.1.6" />

                                    <!--@@@J2.8.1: 報告対象外フラグ-->

                                  </investigationCharacteristic>

                                </subjectOf2>

                              </investigationEvent>

                            </subject>

                          </controlActProcess>

                        </PORR_IN049016UV>

                        <receiver typeCode="RCV">

                          <device classCode="DEV" determinerCode="INSTANCE">

                            <id extension="PMDA" root="2.16.840.1.113883.3.989.2.1.3.14" />

                            <!--N.1.4: Batch Receiver Identifier-->

                          </device>

                        </receiver>

                        <sender typeCode="SND">

                          <device classCode="DEV" determinerCode="INSTANCE">

                            <id extension="JT" root="2.16.840.1.113883.3.989.2.1.3.13" />

                            <!--N.1.3: Batch Sender Identifier-->

                          </device>

                        </sender>

                      </MCCI_IN200100UV01>

                      • 8. Re: How to use XMLQuery to retrieve info from R3 XML
                        tsuji

                        Just make a quick response to disperse the problem you raised in the last post.

                        Also, for some reason, your query throws "missing COLUMNS keyword after the namespace declaration in the xd block.

                        Sure. The xd part should be put right. The mixed up was due to I copy and paste inline and do the editing... The context xpath should of course be the starting point of the 2nd argument, following the first xmlnamespaces.

                        ) xc,
                        xmltable(
                            xmlnamespaces('urn:hl7-org:v3' as "xa"),
                            '/xa:id'
                            PASSING xc.ids
                            COLUMNS
                                safetyreportid varchar2(30) path '@extension'
                        ) xd;
                        

                        With that repositioning, the query helps or not ? Can you make out any lesson out of it ? Something you have to iron out by yourself so that what are left in doubt minimized and we start from there.

                         

                        The xml is long... Tell the forum the salient features on the groups you've in mind to save us some precious time. What output do you want ? etc.

                        • 9. Re: How to use XMLQuery to retrieve info from R3 XML
                          3829237

                          I had to change it like so:

                          select x.xml_id "case_xref",

                          xc.batchmessagenumb, xc.creationTime, xc.messageidentifier, xc.sender, xc.receiver, 

                          xd.safetyreportid, xd.root  

                          from jt_mig.XML_INSERT x,   

                          xmltable(   

                            xmlnamespaces('urn:hl7-org:v3' as "xa"),   

                            '/xa:MCCI_IN200100UV01'   

                            PASSING x.xml_data 

                            COLUMNS 

                              batchmessagenumb varchar2(30) path 'xa:id/@extension', 

                              creationTime varchar2(30) path 'xa:creationTime/@value', 

                              messageidentifier varchar2(30) path 'xa:PORR_IN049016UV/xa:id/@extension', 

                              receiver varchar2(30) path 'xa:PORR_IN049016UV/xa:receiver/xa:device/xa:id/@extension', 

                              sender varchar2(30) path 'xa:PORR_IN049016UV/xa:sender/xa:device/xa:id/@extension', 

                              ids xmltype path 'xa:PORR_IN049016UV/xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id' 

                          ) xc,

                          xmltable(

                            xmlnamespaces('urn:hl7-org:v3' as "xa"),'xa:id'

                            PASSING xc.ids

                            COLUMNS 

                              safetyreportid varchar2(30) path '@extension',

                              root varchar2(35) path '@root'

                          ) xd; 

                           

                          but now it works for that group.  But I need to get it to sort out the repeating IDs, so I can get them into different columns.  In the original example, the XML parser separates them into columns by their @root

                          values, and I am trying to imitate that behavior.

                          • 10. Re: How to use XMLQuery to retrieve info from R3 XML
                            3829237

                            Yes, the query works with the repositioning, and I am very happy with it, thank you.  I would like to be able to add FLWOR to it, but when I add where clauses, it sometimes balks.

                             

                            The one remaining issue is that sometimes within repeating elements, there are duplicate names for child nodes with different purposes in the schema.  Like so:

                             

                                    <investigationEvent classCode="INVSTG" moodCode="EVN">

                                           <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.1" />

                                           <!--C.1.1: Sender's (case) Safety Report Unique Identifier-->

                                           <id extension="JP-JT-BA201900225" root="2.16.840.1.113883.3.989.2.1.3.2" />

                                           <!--C.1.8.1: Worldwide Unique Case Identification Number-->

                                        ...

                                        ... 

                                   </investigationEvent>

                             

                            As you can see from the comments, these are to be two different dtd elements.  How can I pull them both into different columns in the output by leveraging the @root element?

                             

                             

                             

                            • 11. Re: How to use XMLQuery to retrieve info from R3 XML
                              3829237

                              I mean hopefully within the xmltable query specifically, not for the entire query.  Because I would need to be able to get both of those id/@extension elements, and insert them into two different columns in the same query, if possible.

                              • 12. Re: How to use XMLQuery to retrieve info from R3 XML
                                tsuji

                                But I need to get it to sort out the repeating IDs, so I can get them into different columns.

                                As such, the output would be on different rows for each ID. What you want would be something like pivoting ,,, Otherwise, if you want to directly output to a single row, that could not be done by the restriction of a "table" can't take multiple columns of the same name. If you need many more places to have that kind of output, I would say hierarchical structure which is what xml excels a bit would be a better way to go. I mean design the output being by itself an xml including only the desired data (which would be much more smaller) and with manageable hierarchical tree structure in it for human inspection. What's good for a row with many many columns summarizing data for a business or medical report? I don't know...

                                 

                                sql/xml is not particularly good at dynamical column name and varying column counts. But if dbase specializing developers advance that saying ok, I know how many "id" in advance (at design time, not at run-time - which is the normal environment db is working on), it can then be done (see below). But I would say, dynamic parts mentioned which necessitate a run-time approach would be very much the territories of xquery and xslt. The whole problematic can be handled much more at ease with those technologies - I would say,

                                 

                                Having said, suppose at design-time, you already know there are 2 (3 or 4 or more) id nodes embedded in the hl7 document, you can simply do this - 2 for demo only.

                                 

                                select x.xml_id "case_xref", 
                                xc.batchmessagenumb, xc.creationTime, xc.messageidentifier, xc.sender, xc.receiver,  
                                xc.safetyreportid_1, xc.root_1, xc.safeyreportid_2, xs.root_2   
                                from jt_mig.XML_INSERT x,    
                                xmltable(    
                                  xmlnamespaces('urn:hl7-org:v3' as "xa"),    
                                  '/xa:MCCI_IN200100UV01'    
                                  PASSING x.xml_data  
                                  COLUMNS  
                                    batchmessagenumb varchar2(30) path 'xa:id/@extension',  
                                    creationTime varchar2(30) path 'xa:creationTime/@value',  
                                    messageidentifier varchar2(30) path 'xa:PORR_IN049016UV/xa:id/@extension',  
                                    receiver varchar2(30) path 'xa:PORR_IN049016UV/xa:receiver/xa:device/xa:id/@extension',  
                                    sender varchar2(30) path 'xa:PORR_IN049016UV/xa:sender/xa:device/xa:id/@extension',  
                                    safetyreportid_1 varchar2(30) path 'xa:PORR_IN049016UV//(xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id)[1]/@extension'.
                                    root_1 varchar2(35) path 'xa:PORR_IN049016UV//(xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id)[1]/@root',
                                    safetyreportid_2 varchar2(30) path 'xa:PORR_IN049016UV//(xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id)[2]/@extension'.
                                    root_2 varchar2(35) path 'xa:PORR_IN049016UV//(xa:controlActProcess/xa:subject/xa:investigationEvent/xa:id)[2]/@root'
                                ) xc;
                                

                                 

                                You just need to enumerate one by one the id nodes... Here according to the "document order". But it does not necessarily be so. You can more sophisticate filters in the predicats instead of position,