SQL FOR XML with Subroot

Question

I have an vendor's XML schema that I need to duplicate. Here's a sample:

<?xml version="1.0" encoding="us-ascii"?>
<configuration>
  <anialias>
    <ani>
      <name>1025</name>
      <alias>IT-Clint</alias>
      <id>e61174a5-925a-4263-b5db-c71cf00e782f</id>
    </ani>
    <ani>
      <name>7001</name>
      <alias>From Shr 1</alias>
      <id>c608c779-095f-4d20-be24-a0800ba5a6b7</id>
    </ani>
    <ani>
      <name>7002</name>
      <alias>From Sh 2</alias>
      <id>760d669c-e3dc-4bc6-b5ca-37db982f20ec</id>
    </ani>
    <ani>
      <name>7003</name>
      <alias>From Sh 3</alias>
      <id>e337f585-496a-4cc0-8765-429c07fff04d</id>
    </ani>
    <ani>
      <name>1106</name>
      <alias>Commodore</alias>
      <id>1fc8f1d5-2078-4025-917d-5c46c4dc6f85</id>
    </ani>
  </anialias>
</configuration>

Here's my very simple SQL query:

select radioid as name, rtrim(unit_code) as alias,[guid] as id from  @temp ani
order by unit_code
FOR XML auto, root('configuration'),ELEMENTS;

Here's the result of my query:

<configuration>
  <ani>
    <name>2080</name>
    <alias>S11</alias>
    <id>07E1A634-B32C-4348-82C6-088B8E5493F7</id>
  </ani>
  <ani>
    <name>2318</name>
    <alias>S142</alias>
    <id>B7723D04-CD70-4EFB-8BE9-93FA8A78554E</id>
  </ani>
  <ani>
    <name>2441</name>
    <alias>S145</alias>
    <id>03774C65-449D-4176-A7ED-853609272CAB</id>
  </ani>
</configuration>

How can I add the xml declaration at the top and the anialias subroot?

Answer 1

something like that

select '<?xml version="1.0" encoding="us-ascii"?>'
+
(select 
        (select radioid as [name], rtrim(unit_code) as [alias],[guid] as [id] 
         from @temp ani
         order by unit_code
         FOR XML auto, root('anialias'),ELEMENTS, type)
for xml raw('configuration'))