Sql Server FOR XML Fields and Attributes

Question

I need to get an XML from a query:

SELECT
... join ...
FOR XML PATH ('parent-lines'), ROOT('main-tag'), ELEMENTS XSINIL;

I have been able to get this structure:

<main-tag>
  <parent-lines>
    ...
    <child-name>EXAMPLE</child-name>     //case when the child is popolated
    <child-name xsi:nil=true />          //case when the child is empty
    ...
  </parent-lines>
  ...many parent-lines
</main-tag>

Now I have two problems:

• the first is to have the child-names without the xsi: nil attribute in case they are empty so:

<child-name />

• the second is that I have some sort of "header", static which is fine for any parent tag, which I would like to insert in the structure of my XML, and obtain a structure similar to this:

<main-tag>
  <header>
     ...
     <child-name>A sort of explain of the field</child-name>
     ...
  </header>                  //single header
  <parent-lines>...</parent-lines>      //many parent-lines
  ...
</main-tag>

Can you help me?

Answer 1

I solved the problems, the first thanks to @Larnu and his comment, the second using this technique:

SELECT 
 (SELECT 'Cip' AS 'Cip' FOR XML PATH (''), TYPE) AS Header,
 (SELECT 'Ciop' AS 'Ciop' FOR XML PATH ('Parent-lines'), TYPE)
FOR XML PATH (''), ROOT('main-tag');

In the first nested "Select", in the PATH I have not entered anything but on the outside I baptized it with the name I wanted to obtain "AS Header". In the second "Select", however, within the PATH I entered the name I wanted to repeat for each "Parent-lines".

I hope this question / answer will be useful to other users. Thanks again and good luck to all!