SQL Server XML query

I haven't dealt with XML documents since college. I'm looking for some assistance. I need to write a XML query in SQL Server that will output the data as such.

Please note that the data is being pulled from just one table in SQL Server.

<EnrollmentRequest>
    <Vendor id="string"/>
    <Members>
        <Member>  -- 1st Member Record
            <PayerPatientId>string</PayerPatientId>
            <PayerInsuranceId>string</PayerInsuranceId>
            <RequestTypeId>6427</RequestTypeId>
            <LastName>string</LastName>
            <FirstName>string</FirstName>
            <Gender>string</Gender>
            <DOB>string</DOB>
            <Zip>string</Zip>
            <Phone>string</Phone>
            <SSN>string</SSN>
            <City>string</City>
            <State>string</State>
            <DateOfServiceRange/>
            <PatientConsent>string</PatientConsent>
        </Member>
        <Member> -- 2nd Member Record
            <PayerPatientId>string</PayerPatientId>
            <PayerInsuranceId>string</PayerInsuranceId>
            <RequestTypeId>-2065</RequestTypeId>
            <LastName>string</LastName>
            <FirstName>string</FirstName>
            <Gender>string</Gender>
            <DOB>string</DOB>
            <Zip>string</Zip>
            <Phone>string</Phone>
            <SSN>string</SSN>
            <City>string</City>
            <State>string</State>
            <DateOfServiceRange/>
            <PatientConsent>string</PatientConsent>
        </Member>
    </Members>
</EnrollmentRequest>

I tried using

FOR XML PATH('Member'),  
ROOT('EnrollmentRequest')

But I am unable to get the 'Members" element piece. It's my understanding there cannot be more than one root, so how can I get the 'Members' tag to function like the "EnrollmentRequest' tag and how can I get the vendor id tag to appear only once in the file?

2 answers

  • answered 2018-07-20 19:19 Paurian

    Not ideal, but you can do a string replace with a STUFF:

    SELECT
      REPLACE(
        REPLACE(
          STUFF( ( SELECT * FROM Members FOR XML PATH('Member'), ROOT('EnrollmentRequest') ), 1, 0, '' ),
          '<EnrollmentRequest>', '<EnrollmentRequest><Vendor id="string"/><Members>'
        ),
        '</EnrollmentRequest>', '</Members></EnrollmentRequest>'
      )
    

  • answered 2018-07-21 06:31 marc_s

    You need to use a sub-select for your member info - something like this:

    SELECT 
        'abc' AS 'Vendor/@Id',
        (SELECT
            m.ID,
            m.LastName,
            m.FirstName,
            m.ZIP,
            m.City
        FROM 
            dbo.YourMembersTable m
        FOR XML PATH('Member'), TYPE) AS 'Members'
    FOR XML PATH(''), ROOT('EnrollmentRequest')
    

    This enumerates all the members, outputs each in a <Member> element, and then "wraps" this into a <Members> element inside the <EnrollmentRequest> root element.