How can i generate a XML without repeating an field twice

Question

I'm trying to generated a xml using the below code. But I'm not satisfied with the output result. Below code I'm using the cursor to get the ids for the xml to be generated and update in another table. Any help is appreciated and i'm new to xml. Thanks

DECLARE @xml_var XML;
DECLARE @ID INT;
DECLARE XML_CURSOR CURSOR FOR
    SELECT id
    FROM   xml_temp_table
    WHERE  id IS NOT NULL;

OPEN XML_CURSOR;
FETCH NEXT
    FROM  XML_CURSOR
    INTO  @ID;

WHILE @@FETCH_STATUS = 0
BEGIN
  SET @xml_var =
  (
         SELECT
                (
                       SELECT 'Type'    AS ID,
                              'Initial' AS VALUE,
                              ''        AS TAG,
                              'true'    AS VISIBLE,
                              Getdate() AS HISTORY,
                              ''        AS DESCRIPTION,
                              ''        AS COMMENT
                       FROM   XML_TABLE d
                       WHERE  D.XML_ID = @ID FOR XML PATH('field'),
                              TYPE ) AS 'field',
                (
                       SELECT 'OwnerName'   AS ID,
                              'Testing_XML' AS VALUE,
                              ''            AS TAG,
                              'true'        AS VISIBLE,
                              Getdate()     AS HISTORY,
                              ''            AS DESCRIPTION,
                              ''            AS COMMENT
                       FROM   XML_TABLE d
                       WHERE  D.XML_ID = @ID FOR XML PATH('field'),
                              TYPE ) AS 'field'
         FROM   XML_TABLE p
         WHERE  P.XML_ID = @ID FOR XML PATH('Material'),
                ROOT('FormValue') );
  UPDATE S
  SET    S.XML_COL = @xml_var,
  FROM   LOCATION_TABLE_XML S
  WHERE  S.ID = @ID;

  FETCH NEXT
      FROM  XML_CURSOR
      INTO  @ID;

END; 

The result i'm getting is this way

 <FormValue>
<Material>
 <field> ----- i dont want this
  <field>
    <id>Type</id>
    <value>Initial</value>
    <tag />
    <visible>true</visible>
    <history>2016-11-08T16:53:16.440</history>
    <description />
    <comment />
  </field>
  <field>
    <id>OwnerName</id>
    <value>Testing_XML</value>
    <tag />
    <visible>true</visible>
    <history>2016-11-08T16:53:16.440</history>
    <description />
    <comment />
  </field>
</field> ---- i dont want this
 </Material>
</FormValue>

But I want the result in this way

 <FormValue>
 <Material>
  <field>
    <id>Type</id>
    <value>Initial</value>
    <tag />
    <visible>true</visible>
    <history>2016-11-08T16:53:16.440</history>
    <description />
    <comment />
  </field>
  <field>
    <id>OwnerName</id>
    <value>Testing_XML</value>
    <tag />
    <visible>true</visible>
    <history>2016-11-08T16:53:16.440</history>
    <description />
    <comment />
  </field>
  </Material>
  </FormValue>

Answer 1

Might be enough to let the AS 'field' away. Your FOR XML PATH('field') will wrap each row with a <field> element.

The XML returning sub-selects can be seen as scalar values handled like a normal column. By providing a column alias this whole node gets a name and this name is again translated into a wrapping <field> element.

You can either erase this, or replace it with AS [node()] or with AS [*]