SQL Server将表中的数据插入到XML变量中

Question

如何在不使用游标的情况下将大量行插入到XML变量中？ 我知道我可以做

SET @errors.modify('insert <error>{ sql:variable("@text") }</error> as last into /errors[1]') 

插入变量的值，但我想基本上做

SET @errors.modify(SELECT 'insert <error>{ sql:column("text") }</error>' FROM table) 

其中，当然，是不合法的语法。

编辑：显然我的问题不清楚。我想是能够做到这样的：

CREATE TABLE my_table(text nvarchar(50)) 
INSERT INTO my_table VALUES('Message 2') 
INSERT INTO my_table VALUES('Message 3') 

DECLARE @errors xml 
SET @errors = '<errors><error>Message 1</error></errors>' 

SET @errors.modify('INSERT EVERYTHING FROM my_table MAGIC STATEMENT') 

并运行此代码后@errors应该包含

<errors> 
    <error>Message 1</error> 
    <error>Message 2</error> 
    <error>Message 3</error> 
</errors> 

Answer 1

基于马克的答案，这里是一个为SQL Server 2005的有效的解决方案：

CREATE TABLE #my_table(text nvarchar(50)) 
INSERT INTO #my_table VALUES('Message 2') 
INSERT INTO #my_table VALUES('Message 3') 

DECLARE @errors xml 
SET @errors = '<errors><error>Message 1</error></errors>' 

SELECT @errors = CAST(@errors AS nvarchar(max)) + '<new>' + (SELECT text AS 'error' FROM #my_table FOR XML PATH(''), ELEMENTS) + '</new>' 

SET @errors = CAST(@errors AS nvarchar(max)) + '<new>' + @newErrors + '</new>' 
SET @errors.modify('insert (/new/*) as last into (/errors)[1]') 
SET @errors.modify('delete (/new)') 

SELECT @errors 

DROP TABLE #my_table 

将返回

<errors> 
    <error>Message 1</error> 
    <error>Message 2</error> 
    <error>Message 3</error> 
</errors> 

Answer 2

最后更新：

OK，现在的问题是，更清楚，他是解决方案 - 希望！

DECLARE @errors xml 
SET @errors = '<errors><error>Message 1</error></errors>' 

DECLARE @newErrors XML 

SELECT @newErrors = (SELECT text AS 'error' 
FROM dbo.my_table 
FOR XML PATH(''), ELEMENTS) 

SELECT @errors, @newErrors 

SET @errors.modify('insert sql:variable("@newErrors") as last into (/errors)[1]') 

SELECT @errors 

这给了我一开始

@errors后

<errors><error>Message 1</error></errors> 

@newError “魔术师” 选择：

<error>Message 2</error><error>Message 3</error> 

@errors的更新后：

<errors> 
    <error>Message 1</error> 
    <error>Message 2</error> 
    <error>Message 3</error> 
</errors> 

是那个你在找什么？ :-)

---

（旧的答案 - 不是什么OP是寻找.....）

你需要看看SQL的XQuery的.nodes()功能 - 这将打破一个XML 根据XPath表达式（引用XML中的某个点，您可能拥有相同结构的节点的枚举），将其变为XML节点列表，并为其提供“虚拟”表和列名称。

基于该“Table.Column”元素，您可以从该XML节点中选择单个值 - 属性或子元素 - 并将其返回为“原子”值。作为INT，VARCHAR（x），无论你需要什么。这些数值可以被插入到表：

INSERT dbo.YourTable(col1, col2, col3, ..., colN) 
    SELECT 
    Error.Column.value('@attr1[1]', 'varchar(20)'), 
    Error.Column.value('subitem[1]', 'int'), 
    ..... 
    Error.Column.value('subitemN[1]', 'DateTime') 
    FROM 
    @xmldata.nodes('/error') AS Error(Column) 

UPDATE：好了，你想要做的恰恰相反 - 把关系型数据转换成XML - 这甚至:-)

DECLARE @NewXmlContent XML 

SELECT @NewXmlContent = 
     (SELECT 
      col1 as '@ID', 
      col2 as 'SomeElement', 
      ..... 
      colN as 'LastElement' 
     FROM 
      dbo.YourTable 
     WHERE 
      .... 
     FOR XML PATH('element'), ROOT('root') 
     ) 

UPDATE YourOtherTable 
SET XmlField.modify('insert sql:variable("@NewXmlContent") 
        as last into (/XPath)[1]') 
WHERE (some condition) 

这更容易会给你@NewXmlContent是这样的：

<root> 
    <element ID="(value of col1)"> 
     <SomeElement>(value of col2)</SomeElement> 
     ..... 
     <LastElement>(value of colN)</LastElement> 
    </element> 
</root> 

和UPDATE语句与.modify()通话将实际插入次在内容中添加到数据库中的现有XML字段中。这是将XML内容转换为现有XML列的唯一方法 - 无法直接引用正在插入的XML片段中的另一个XML列......

新的“FOR XML PATH”语法非常强大，灵活，并允许你做任何事情。

当然，您可以轻松地将其存储到XML变量中。

马克

Answer 3

这难道不更简单？

set ErrorXML=(SELECT * from #MyTable FOR XML AUTO)