How to export formatted XML output by using SQL Server

Question

I try to do formatted XML output using SQL Server 2016.

This is my code:

SELECT *
FROM [GRADUATE]
FOR XML PATH('applicant'), ROOT('applicantsuniversity'), ELEMENTS XSINIL;

The output is

The output I have to submit needs to look like this:

Question #1: how can I put this line to show in my code?

Question #2: how to treat null values?

How can I adapt my code to show an output like this:

not like this:

Thanks for the help.

Answer 1

You need put the whole thing in a subquery and create the root element manually.

Use the , TYPE option to embed XML within other XML queries. The subquery should not have a column name, or you will get a second level of nesting.

Use @ in the column name to specify an attribute.

Remove the , ELEMENTS XSINIL to get rid of xsi:nil attributes. If you actually want to show empty elemnts then you can do ISNULL(maidenName, '')

SELECT
  [@university] = '123456',
  (
    SELECT g.*
    FROM GRADUATE g
    FOR XML PATH('applicant'), TYPE
  )
FOR XML PATH('applicants');

If you actually need to get that university attribute from the data then you need some kind of aggregation. STRING_AGG would make sense if you have it in your version, but you do need to cast it back to XML afterwards.

SELECT
  [@university] = g.university,
  CAST(g.xmlData AS xml)
FROM (
    SELECT
      g.university,
      xmlData = STRING_AGG(x.xmlData, '')
    FROM GRADUATE g
    CROSS APPLY
    (
        SELECT g.*
        FOR XML PATH('applicant')
    ) x(xmlData)
) g
FOR XML PATH('applicants');