Post reply

create XML

  • March 20, 2024 at 3:51 am

    #4398560

    Hello..

    I want to create an XML file with the below structure.

    I have all the elements in respective tables.

    And I am using FOR XML AUTO , ELEMENTS to generate the file.

    My only issue is that I am not able to generate the incremental recordIdentifier tag that increases in value under each node

    Sample XML file is attached.

     

    <ns1:edgeServerEnrollmentSubmission xmlns:ns1="http://vo.edge.fm.cms.hhs.gov">

    <ns1:fileIdentifier>143040116215</ns1:fileIdentifier>

    <ns1:executionZoneCode>P</ns1:executionZoneCode>

    <ns1:interfaceControlReleaseNumber>02.01.03</ns1:interfaceControlReleaseNumber>

    <ns1:generationDateTime>2022-10-01T10:00:00</ns1:generationDateTime>

    <ns1:submissionTypeCode>E</ns1:submissionTypeCode>

    <ns1:insuredMemberTotalQuantity>2</ns1:insuredMemberTotalQuantity>

    <ns1:insuredMemberProfileTotalQuantity>2</ns1:insuredMemberProfileTotalQuantity>

    <ns1:includedEnrollmentIssuer>

    <ns1:recordIdentifier>1</ns1:recordIdentifier>

    <ns1:issuerIdentifier>10921</ns1:issuerIdentifier>

    <ns1:issuerInsuredMemberTotalQuantity>2</ns1:issuerInsuredMemberTotalQuantity>

    <ns1:issuerInsuredMemberProfileTotalQuantity>2</ns1:issuerInsuredMemberProfileTotalQuantity>

    <ns1:includedInsuredMember>

    <ns1:recordIdentifier>2</ns1:recordIdentifier>

    <ns1:insuredMemberIdentifier>TC_02_Adult_S</ns1:insuredMemberIdentifier>

    <ns1:insuredMemberBirthDate>1954-12-12</ns1:insuredMemberBirthDate>

    <ns1:insuredMemberGenderCode>M</ns1:insuredMemberGenderCode>

    <ns1:insuredMemberRace>06</ns1:insuredMemberRace>

    <ns1:insuredMemberEthnicity>25</ns1:insuredMemberEthnicity>

    <ns1:includedInsuredMemberProfile>

    <ns1:recordIdentifier>3</ns1:recordIdentifier>

    <ns1:subscriberIndicator>S</ns1:subscriberIndicator>

    <ns1:subscriberIdentifier/>

    <!-- 10921CO013000103 is an Individual market plan -->

    <ns1:insurancePlanIdentifier>10921CO013000103</ns1:insurancePlanIdentifier>

    <ns1:coverageStartDate>2022-01-01</ns1:coverageStartDate>

    <ns1:coverageEndDate>2022-12-31</ns1:coverageEndDate>

    <ns1:enrollmentMaintenanceTypeCode>021028</ns1:enrollmentMaintenanceTypeCode>

    <ns1:insurancePlanPremiumAmount>1000.00</ns1:insurancePlanPremiumAmount>

    <ns1:rateAreaIdentifier>001</ns1:rateAreaIdentifier>

    <ns1:zipCode>22033</ns1:zipCode>

    <ns1:federalAPTC>0</ns1:federalAPTC>

    <ns1:statePremiumSubsidy>0</ns1:statePremiumSubsidy>

    <ns1:stateCSR>0</ns1:stateCSR>

    <ns1:ICHRA_QSEHRA>I</ns1:ICHRA_QSEHRA>

    <ns1:QSEHRA_Spousal>N</ns1:QSEHRA_Spousal>

    <ns1:QSEHRA_Medical>N</ns1:QSEHRA_Medical>

    </ns1:includedInsuredMemberProfile>

    </ns1:includedInsuredMember>

    <ns1:includedInsuredMember>

    <ns1:recordIdentifier>4</ns1:recordIdentifier>

    <ns1:insuredMemberIdentifier>TC_02_Adult_NS</ns1:insuredMemberIdentifier>

    <ns1:insuredMemberBirthDate>1955-07-12</ns1:insuredMemberBirthDate>

    <ns1:insuredMemberGenderCode>F</ns1:insuredMemberGenderCode>

    <ns1:insuredMemberRace>06</ns1:insuredMemberRace>

    <ns1:insuredMemberEthnicity>25</ns1:insuredMemberEthnicity>

    <ns1:includedInsuredMemberProfile>

    <ns1:recordIdentifier>5</ns1:recordIdentifier>

    <ns1:subscriberIndicator/>

    <ns1:subscriberIdentifier>TC_02_Adult_S</ns1:subscriberIdentifier>

    <ns1:insurancePlanIdentifier>10921CO013000103</ns1:insurancePlanIdentifier>

    <ns1:coverageStartDate>2022-01-01</ns1:coverageStartDate>

    <ns1:coverageEndDate>2022-12-31</ns1:coverageEndDate>

    <ns1:enrollmentMaintenanceTypeCode>021028</ns1:enrollmentMaintenanceTypeCode>

    <ns1:insurancePlanPremiumAmount>0.00</ns1:insurancePlanPremiumAmount>

    <ns1:rateAreaIdentifier>001</ns1:rateAreaIdentifier>

    <ns1:zipCode/>

    <ns1:federalAPTC/>

    <ns1:statePremiumSubsidy/>

    <ns1:stateCSR/>

    <ns1:ICHRA_QSEHRA/>

    <ns1:QSEHRA_Spousal/>

    <ns1:QSEHRA_Medical/>

    </ns1:includedInsuredMemberProfile>

    </ns1:includedInsuredMember>

    </ns1:includedEnrollmentIssuer>

    </ns1:edgeServerEnrollmentSubmission>

     

     

    Attachments:
    You must be logged in to view attached files.

  • March 20, 2024 at 10:17 am

    #4398755

    Can you show us the T-SQL which generates this?

    Using ROW_NUMBER() might do what you want, but I can't be sure.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • March 21, 2024 at 6:45 pm

    #4399662

    The XML I have shown is a sample file that I have to create.

    I dont have the t-sql code. I need T-SQL to generate such file.

    How ever, I am attaching the T-SQL code I created to generate such file.

    I have all the elements defined except the recordId, which I have kept as blank ('' as RecordId)

    I need to make changes to the attached query to generate the recodId

    Attachments:
    You must be logged in to view attached files.

  • March 22, 2024 at 10:53 am

    #4400051

    Something like this?

    DROP TABLE IF EXISTS #SomeData;

    CREATE TABLE #SomeData
    (
        Col1 VARCHAR(100) NOT NULL
       ,Col2 VARCHAR(100) NOT NULL
    );

    INSERT #SomeData
    (
        Col1
       ,Col2
    )
    VALUES
    ('a', 'a')
    ,('b', 'b');

    WITH XMLNAMESPACES
    (
        'http://vo.edge.fm.cms.hhs.gov' AS ns1
    )
    SELECT [ns1:Col1] = [ns1:edgeServerEnrollmentSubmission].Col1
          ,[ns1:Col2] = [ns1:edgeServerEnrollmentSubmission].Col2
          ,[ns1:RecordIdentifier] = ROW_NUMBER () OVER (ORDER BY [ns1:edgeServerEnrollmentSubmission].Col1)
    FROM #SomeData [ns1:edgeServerEnrollmentSubmission]
    FOR XML AUTO, ELEMENTS;

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

Viewing 4 posts - 1 through 3 (of 3 total)

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