代码之家 › 专栏 › 技术社区 › erikkallen

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

sqlxml sql-server-2005

erikkallen · 技术社区 · 15 年前

如何在不使用光标的情况下将整行插入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>
  <error>Message 1</error>
  <error>Message 2</error>
  <error>Message 3</error>
</errors>

3 回复 | 直到 13 年前

Samuel Harmer 13 年前

这不简单吗?

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

marc_s 15 年前

最后更新:

好吧,现在问题更清楚了,他就是解决办法-希望如此!!

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><error>Message 1</error></errors>

@“magic”选择后出现新错误:

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

@更新后出错:

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

是那你在找什么??)

(旧的答案-不是操作人员想要的…)

你需要看看 .nodes() SQL XQuery中的函数-这将分解XML 变量转换为XML节点列表,基于xpath表达式(它引用了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)

更新: 好的,所以您要做的恰恰相反—将关系数据转换为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>

和更新语句 .modify() 调用实际上会将该内容插入数据库中现有的XML字段中。这是将XML内容获取到现有XML列中的唯一方法-无法直接引用正在插入的XML片段中的另一个XML列….

新的“for XML path”语法非常强大和灵活,允许您做任何事情。

当然,您可以很容易地将其存储到XML变量中。

马克

erikkallen 15 年前

基于Marc的答案,下面是一个适用于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>