staging XML data into SQL server

  • I have a scenario where I have to read XML file and stage the data. My XML file:

    <patientRequest>

    <Point>RequestDetails</Point>

    <requestID>000006</requestID>

    <requestType>PATIENT</requestType>

    <claimNumber />

    <SourceSystemId>XXXXX</SourceSystemId>

    <Status code="01">Approved</Status>

    <member>

    <memberID>1000000</memberID>

    </member>

    <facility>

    <ID>000000</ID>

    <ProviderID>12345678</ProviderID>

    <secondaryIDs>

    <secondaryID code="00">12345</secondaryID>

    </secondaryIDs>

    </facility>

    <urgency code="X">eeeeee</urgency>

    <requestDiagnosis>

    <requestDiagnosis>

    <primaryFlag>false</primaryFlag>

    <code>11111</code>

    <codeType>0</codeType>

    <description>hggfaa</description>

    <sequence>2</sequence>

    </requestDiagnosis>

    </requestDiagnosis>

    <patientStayRequest>

    <patientStayRequestDetailsList>

    <patientStayRequestDetails>

    <initialRequestFlag>true</initialRequestFlag>

    <units>5</units>

    <notifyDate>1900-01-01</notifyDate>

    <startDate>1900-01-01</startDate>

    <requestItemDetailSequence>1</requestItemDetailSequence>

    <attendingProvider>

    <ID>10000000</ID>

    <providerName>XXXXXXX</providerName>

    <secondaryIDs>

    <secondaryID code="00">CCCCCCCC</secondaryID>

    </secondaryIDs>

    </attendingProvider>

    </patientStayRequestDetails>

    </patientStayRequestDetailsList>

    </patientStayRequest>

    </patientRequest>

    I have staged data into different tables using OPENXML .

    table 1-patientrequest

    table 2-member

    table 3-facility

    table 4-requestDiagnosis

    table 5-patientStayRequest

    table 6-attendingProvider

    Now I want to combine all the data in one table. I am not able to check for values in different tables(not understanding which join to use). for example, for requestid 000006, I want to have facilityID and AttendingproviderID in one table.

    I tried joining facility table and attending provider tables on requestid, but this fails when any requestId does not have either of the one or both. XMl file can/cannot have these values. How do I combine all columns in one table? I have requestid in all the tables.

    This is just an example of Source file but my source file has more nodes than this.

    Any help is appreciated!!

Viewing 0 posts

You must be logged in to reply to this topic. Login to reply