如何在不重复字段两次的情况下生成XML ?

Question

我试图使用下面的代码生成xml。但是我对输出结果并不满意。下面的代码中，我使用游标获取要在另一个表中生成和更新的xml的id。任何帮助是感激的，我是新的xml。由于

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; 

我得到的结果是这样的

 <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>

但是我想要这样的结果

 <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

可能足以让AS 'field'离开。您的FOR XML PATH('field')将用<field>元素包装每一行。

返回的XML子选择可以看作是像普通列一样处理的标量值。通过提供列别名，整个节点获得一个名称，该名称再次转换为包装<field>元素。

您可以将其删除，或者将其替换为AS [node()]或AS [*]