SQL Server XML query

Question

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?

marc_s · Accepted Answer · 2018-07-21 06:31:45Z

1

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.

answered Jul 21, 2018 at 6:31

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

teck_neck123 Over a year ago

That did it. i was in XML hell, thank you it Worked!

marc_s Over a year ago

@teck_neck123: if this answer helped you solve your problem, then please accept this answer. This will show your appreciation for the people who spent their own time to help you.

Collectives™ on Stack Overflow

SQL Server XML query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related