create XML

Question

Post reply

create XML

vineet1234

Valued Member

Points: 63
More actions
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/>

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

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

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

Phil Parkin SSC Guru Points: 246976 More actions · Answer 1

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

vineet1234 Valued Member Points: 63 More actions · Answer 2

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.

Phil Parkin SSC Guru Points: 246976 More actions · Answer 3

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