从Sql Server语句的默认输出中删除根XML节点

I want @MyActualXMLOut to look like @MyDesiredXMLOut... How? Thanks in advance!

我希望@MyActualXMLOut看起来像@MyDesiredXMLOut…如何?提前谢谢!

@MyDesiredXMLOut =

<MyRequiredRoot>
  <Property1>Value1</Property1>
  <Property2>Value2</Property2>
</MyRequiredRoot>

@MyActualXMLOut

<_x0040_MyTableVar>
  <MyXML>
    <MyRequiredRoot>
      <Property1>Value1</Property1>
      <Property2>Value2</Property2>
    </MyRequiredRoot>
  </MyXML>
</_x0040_MyTableVar>

The code below can be run as is...

下面的代码可以运行为……

DECLARE @MyDesiredXMLOut XML;
DECLARE @MyActualXMLOut XML;

SELECT @MyDesiredXMLOut =
   CONVERT( XML, 
   '<MyRequiredRoot><Property1>Value1</Property1>
      <Property2>Value2</Property2>
    </MyRequiredRoot>' );

DECLARE @MyTableVar table( ID int NOT NULL, MyXML XML NOT NULL );

INSERT INTO @MyTableVar VALUES( 1, @MyDesiredXMLOut )

SELECT @MyActualXMLOut = 
    ( SELECT MyXML
      FROM   @MyTableVar
      WHERE  ID = 1
      FOR XML AUTO )

SELECT @MyDesiredXMLOut;

SELECT @MyActualXMLOut;

3 个解决方案

#1

You can use query('/')

您可以使用查询(“/”)

SELECT  @MyActualXMLOut = 
        (   SELECT  MyXML.query ('/') 
            FROM    @MyTableVar 
            WHERE   ID = 1
            FOR     XML PATH('')
        )

#2

FOR XML AUTO is trying to add information about your table name (which likely contains characters that aren't valid XML element names) and the column name it came from.

对于XML AUTO，它试图添加关于表名(可能包含无效XML元素名称的字符)和它来自的列名的信息。

Change

改变

SELECT @MyActualXMLOut = 
    ( SELECT MyXML
      FROM   @MyTableVar
      WHERE  ID = 1
      FOR XML AUTO )

来

SELECT @MyActualXMLOut = 
    ( SELECT MyXML as '*'
      FROM   @MyTableVar
      WHERE  ID = 1
      FOR XML PATH('') )

Explanation: as '*' tells SQL Server that you just want the column value directly, don't use the column name as a tag name; FOR XML PATH('') says you don't want to add any additional root node around the output, just use as is.

说明:“*”告诉SQL Server您只想要列值，不要使用列名作为标记名;对于XML PATH(”)来说，您不希望在输出周围添加任何附加的根节点，只要按原样使用即可。

#3

I don't know if this is real life example, but:

我不知道这是不是现实生活中的例子，但是:

The value in the table is the XML already...

表中的值已经是XML了……

Leave away the FOR XML AUTO (Anyway as pointed out one should prefer PATH):

去掉FOR XML AUTO(不管怎样，正如前面指出的，应该选择PATH):

SELECT @MyActualXMLOut = 
    ( SELECT MyXML
      FROM   @MyTableVar
      WHERE  ID = 1);

Or even simpler

甚至更简单

SELECT @MyActualXMLOut = MyXML
FROM   @MyTableVar
WHERE  ID = 1;

#1