时间戳

首页 > 解决方案 > 在 SQL xml 属性值中使用局部变量

sql-server - 在 SQL xml 属性值中使用局部变量

问题描述

我重新使用了一些代码来将单个字段中的逗号分隔字符串解析为单独的字段。
我现在想合并循环以使用局部变量对新字段进行编号（我认为这是最好的方法）以及在单例值 [1] 等中使用它们。

[1] 将是 [WT1]，[2] 将是 [SA1]，[3]=[WT2]，[4]=[SA2] 等

循环的数量需要由局部变量设置并且会改变，所以我不能只为输出创建有限数量的新字段。

我已经能够使用局部变量来替换 NewXml.value 字符串中的“属性”，但无法管理单例值的语法。

我想我需要将变量转换为字符串。我还想将它合并到输出新字段名称的循环中，例如。WT1、SA1、WT2、SA2 等

我已经注释掉了循环代码行，以及 SELECT 中我无法工作的第一个字段 (WT1)，这与现有 WT1 的行相同。

我希望我已经清楚地解释了自己的解决方案。

DECLARE @t TABLE(
ProductId INT,
ProductName VARCHAR(25),
SupplierId INT,
Descr VARCHAR(50)
)

INSERT INTO @t VALUES (1,'Product1',1,'A1,10in,30in,2lbs');
INSERT INTO @t VALUES (2,'Product2',2,'T6,15in,30in,');
INSERT INTO @t VALUES (3,'Product3',1,'A2,1in,,0.5lbs');


declare @i  int;
set @i=1;

declare @prodatt VARCHAR(25);
set @prodatt ='Attribute';
--while @i < 8
--begin
SELECT 
--  NewXML.value('(/Product[1]/Attribute[*[local-name() = sql:variable("@i")])]','varchar(25)') AS [WT1],
    NewXML.value('(/Product[1]/*[local-name() = sql:variable("@prodatt")])[2]','varchar(25)') AS [SA1],

    NewXML.value('/Product[1]/Attribute[1]','varchar(25)') AS [WT1],
--  NewXML.value('/Product[1]/Attribute[2]','varchar(25)') AS [SA1],
    NewXML.value('/Product[1]/Attribute[3]','varchar(25)') AS [WT2],
    NewXML.value('/Product[1]/Attribute[4]','varchar(25)') AS [SA2],
    [ProductID],
    [SupplierId]
FROM @t t1
CROSS APPLY (SELECT XMLEncoded=(SELECT Descr AS [*] FROM @t t2 WHERE t1.ProductId = t2.[ProductId] FOR XML PATH(''))) EncodeXML
CROSS APPLY (SELECT NewXML=CAST('<Product><Attribute>'+REPLACE(XMLEncoded,',','</Attribute><Attribute>')+'</Attribute></Product>' AS XML)) CastXML

标签： sql-serverxmlvariableslocal

解决方案

这是更清洁的实现。如果要使其绝对动态，则需要使用动态 SQL。

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(25),
    SupplierId INT,
    Descr VARCHAR(50)
);

INSERT INTO @tbl VALUES 
(1,'Product1',1,'A1,10in,30in,2lbs')
, (2,'Product2',2,'T6,15in,30in,')
, (3,'Product3',1,'A2,1in,,0.5lbs');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = ',';

-- Method #1, via XML
;WITH rs AS
(
   SELECT ProductID, Descr
      , TRY_CAST(N'<root><r><![CDATA[' + 
         REPLACE(Descr, @separator, ']]></r><r><![CDATA[') +
            ']]></r></root>' AS XML) AS xmldata
   FROM @tbl
)
SELECT ProductID, Descr
   , xmldata.value('(/root/r[1]/text())[1]','VARCHAR(10)') AS WT1
   , xmldata.value('(/root/r[2]/text())[1]','VARCHAR(10)') AS SA1
   , xmldata.value('(/root/r[3]/text())[1]','VARCHAR(10)') AS WT2
   , xmldata.value('(/root/r[4]/text())[1]','VARCHAR(10)') AS SA2
   , xmldata.value('(/root/r[5]/text())[1]','VARCHAR(10)') AS WT3
   , xmldata.value('(/root/r[6]/text())[1]','VARCHAR(10)') AS SA3
FROM rs;

输出

+-----------+-------------------+-----+------+------+--------+------+------+
| ProductID |       Descr       | WT1 | SA1  | WT2  |  SA2   | WT3  | SA3  |
+-----------+-------------------+-----+------+------+--------+------+------+
|         1 | A1,10in,30in,2lbs | A1  | 10in | 30in | 2lbs   | NULL | NULL |
|         2 | T6,15in,30in,     | T6  | 15in | 30in | NULL   | NULL | NULL |
|         3 | A2,1in,,0.5lbs    | A2  | 1in  | NULL | 0.5lbs | NULL | NULL |
+-----------+-------------------+-----+------+------+--------+------+------+

推荐阅读