主页 / user-13483388

Leah's questions

Martin Hope
Leah
Asked: 2025-01-20 23:10:01 +0800 CST
  • 7

我有一个XML包含表的变量。该表有若干列和一行。在前两列中，我需要使用存储过程的结果更新它们的值 - 如果值为空则插入一个值，如果值已存在则更新该值。

这是我目前所得到的。请注意，表中元素的名称/结构与实际情况一致（即，所有行都称为“行”，所有列都称为“列”）。这是由于输出 XML 表的程序的限制。

-- Declare xml variable. Either column could be empty or populated. They are shown empty here.
DECLARE @x xml = N'<?xml version="1.0" encoding="utf-16"?>
<Table>
  <Row>
    <Column></Column>
    <Column></Column>
    <Column>some data</Column>
    <Column>some more data</Column>
  </Row>
</Table>'

-- View xml table prior to modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

-- Declare new id and date variables. This will come from a stored procedure. I've made it static to simplify the example.
DECLARE @id int = 24
DECLARE @date datetime2(2) = sysutcdatetime()

-- If id is empty, insert the id value. Otherwise, update the id value.
IF (SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@id")} into (//Table/Row/Column)[1]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[1] with sql:variable("@id")')

-- If date is empty, insert the date value. Otherwise, update the date value
IF (SELECT tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
    FROM @x.nodes('//Table/Row') tbl(col)) IS NULL

    SET @x.modify('insert text{sql:variable("@date")} into (//Table/Row/Column)[2]')

ELSE
    
    SET @x.modify('replace value of (//Table/Row/Column/text())[2] with sql:variable("@date")')

-- View xml table after modifications
SELECT tbl.col.value('Column[1]/text()[1]', 'int') 'id'
      ,tbl.col.value('Column[2]/text()[1]', 'datetime2(2)') 'date'
      ,tbl.col.value('Column[3]/text()[1]', 'varchar(50)') 'data'
      ,tbl.col.value('Column[4]/text()[1]', 'varchar(50)') 'more data'
FROM @x.nodes('//Table/Row') tbl(col)

这似乎可行；但是，我想知道是否有办法在修改函数本身中写入条件（即，如果为空，则插入；否则，替换值）？换句话说，我可以避免使用 SQLIF...ELSE语句而使用一个SET语句吗？

另外，有没有办法在同一个修改函数中更新这两列，还是必须分开？

提前致谢！

编辑改变的示例代码来证明该表可能有额外的列并阐明 XML 表的格式。

sql-server
Martin Hope
Leah
Asked: 2024-11-08 06:19:30 +0800 CST
  • 8

我正在尝试从 SQL 表中选择值作为具有特定格式的 XML 表。

使用下表...

SELECT x.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) x(Id, FirstName, LastName)

SELECT *
FROM #t

DROP TABLE #t

我需要一个看起来像这样的 XML 输出......

<Table>
  <Row>
    <Column>Id</Column>
    <Column>FirstName</Column>
    <Column>LastName</Column>
  </Row>
  <Row>
    <Column>1</Column>
    <Column>John</Column>
    <Column>Doe</Column>
  </Row>
</Table>

我能够使用 UNION 和 XML PATH 获得与此匹配的输出...

SELECT t1.*
INTO #t
FROM (VALUES (1, 'John', 'Doe')) t1(Id, FirstName, LastName);

WITH t2(xmlTable) AS (
        SELECT t3.Id 'Column'
              ,''
              ,t3.FirstName 'Column'
              ,''
              ,t3.LastName 'Column'
        FROM (
            SELECT *
            FROM (VALUES ('Id', 'FirstName', 'LastName')) t4(Id, FirstName, LastName)
            UNION ALL
            SELECT CONVERT(varchar(15), #t.Id)
                  ,CONVERT(varchar(15), #t.FirstName)
                  ,CONVERT(varchar(15), #t.LastName)
            FROM #t) t3
        FOR XML PATH ('Row'), ROOT('Table'))

SELECT t2.xmlTable
FROM t2

DROP TABLE #t

但这似乎非常麻烦。有没有更优雅的解决方案？具体来说，我可以不使用 UNION 来做到这一点，也许只使用 XML PATH？

sql