如何在xml中删除重复字段

I have a table with a column type XML, the xml files in that column has duplicate field which I want to remove. Please let me know how can I update that column accordingly.

我有一个具有列类型XML的表，该列中的XML文件有重复字段，我想删除这些字段。请让我知道如何相应地更新该专栏。

<Material_NOI>
<Field> -------- ? I want to delete this duplicate one.
  <Field>
    <id>Type</id>
    <value>Initial</value>
    <tag />
    <visible>true</visible>
    <history>10/8/2015|</history>
    <description />
    </Field>
    </Field>
 </Material_NOI>

2 个解决方案

#1

Easiest was to take the needed node with .query() and rebuild the XML

最简单的方法是使用.query()和重建XML。

DECLARE @XML XML=
N'<Material_NOI>
  <Field>
    <Field>
      <id>Type</id>
      <value>Initial</value>
      <tag />
      <visible>true</visible>
      <history>10/8/2015|</history>
      <description />
    </Field>
  </Field>
</Material_NOI>';

 SELECT @XML.query('/Material_NOI/Field/Field')
 FOR XML PATH('Material_NOI')

UPDATE: Same with table-data

You might use an updateable CTE like this:

您可以使用这样的可更新CTE:

DECLARE @tbl TABLE(XmlColumn XML);
INSERT INTO @tbl VALUES
(N'<Material_NOI>
  <Field>
    <Field>
      <id>Type</id>
      <value>Initial</value>
      <tag />
      <visible>true</visible>
      <history>10/8/2015|</history>
      <description />
    </Field>
  </Field>
</Material_NOI>')
,(N'<Material_NOI>
  <Field>
    <Field>
      <id>Another id</id>
      <value>One more</value>
      <tag />
      <visible>false</visible>
      <history>10/8/2015|</history>
      <description />
    </Field>
  </Field>
</Material_NOI>');

WITH UpdateableCTE AS
(
    SELECT XmlColumn
          ,(SELECT XmlColumn.query('/Material_NOI/Field/Field') FOR XML PATH('Material_NOI'),TYPE) AS NewValue
    FROM @tbl
)
UPDATE UpdateableCTE SET XmlColumn=NewValue;

SELECT * FROM @tbl;

UPDATE 2: Short version using FLWOR-query

UPDATE @tbl SET XmlColumn=XmlColumn.query(N'<Material_NOI>
                                            {
                                            for $f in /Material_NOI/Field/Field 
                                                return $f
                                            }
                                            </Material_NOI>');

#2

Using XQuery. This script is only going to work if all rows have the extra element. Make sure you make a copy of the table before you run this, you never know.

使用XQuery。只有当所有的行都有额外的元素时，这个脚本才会工作。在运行这个之前，一定要复制表，你永远不会知道。

DECLARE @xml_table TABLE (x XML);
INSERT INTO @xml_table(x)VALUES(
'<Material_NOI>
  <Field>
  <Field>
    <id>Type</id>
    <value>Initial</value>
    <tag />
    <visible>true</visible>
    <history>10/8/2015|</history>
    <description />
    </Field>
    </Field>
 </Material_NOI>');

UPDATE
    @xml_table
SET
    x=x.query('<Material_NOI>{for $p in Material_NOI/Field/Field return $p}</Material_NOI>');

SELECT * FROM @xml_table;