代码之家 › 专栏 › 技术社区 › V Krishan

串联nvarchar列并转换为XML

xml sql-server

V Krishan · 技术社区 · 8 年前

我当前正在获取一个整数行,然后将该行转换为哈希表中的Nvarchar,然后将其转换为XML。其目的是在一个XML变量中返回所有值,如下所示:

<item id ="001"/><item id ="002"/><item id ="003"/><item id ="004"/><item id ="005"/>

当前,我的代码将其作为XML行返回,如下所示:

 col
---------------
<item id="60114" />
<item id="60116" />
<item id="60120" />
<item id="60122" />
<item id="60123" />
<item id="60124" />
<item id="60125" />
<item id="60129" />

这是我的代码,我已经动画化了:

DROP TABLE #ClientNumber

DECLARE @XMLClientID NVARCHAR (MAX)

CREATE TABLE #ClientNumber (ID int identity(1,1), [XMLClientID] NVARCHAR(20))

INSERT INTO #ClientNumber 
SELECT '<item id ="'+ CAST([ClientId] AS NVARCHAR) + '"/>' AS [XMLClientID] FROM [dbo.].[MyView] WHERE Column = 'Condition' AND [ClientName] LIKE 'BLA%';


WITH xoutput AS (
  SELECT CONVERT(xml, [XMLClientID]) AS col
    FROM #ClientNumber)
SELECT *
 FROM xoutput

任何转向都很好,因为由于for XML,这些东西都无法工作。

3 回复 | 直到 8 年前

McNets 8 年前

您可以使用整数值,而不是转换和格式化它。

 CREATE TABLE tbl (id int);

 INSERT INTO tbl VALUES (60114), (60116), (60120), (60122)
 GO

 SELECT id
 FROM tbl item
 FOR XML AUTO

输出:

| XML_F52E2B61-18A1-11d1-B105-00805F49916B                                 |
| :----------------------------------------------------------------------- |
| <item id="60114"/><item id="60116"/><item id="60120"/><item id="60122"/> |

D小提琴 here

MichaÅ Turczyn 8 年前

尝试以下操作:

declare @table table (col varchar(100))
insert into @table values
('<item id="60114" />'),
('<item id="60116" />'),
('<item id="60120" />'),
('<item id="60122" />'),
('<item id="60123" />'),
('<item id="60124" />'),
('<item id="60125" />'),
('<item id="60129" />')

select cast(col as xml) from @table for xml path('')

V Krishan 8 年前

感谢@John Cappelletti提供的链接。以下是我的解决方案:

DECLARE @XMLClientID VARCHAR (MAX)
DECLARE @StringClientID VARCHAR (MAX)
DECLARE @XMLStringClient XML;


CREATE TABLE #ClientNumber (ID int identity(1,1), [XMLClientID] VARCHAR(20))

INSERT INTO #ClientNumber
--AMEND LINE BELOW TO SELECT TARGETS!!--
SELECT '<item id ="'+ CAST([ClientId] AS NVARCHAR) + '"/>' AS [XMLClientID] FROM [dbo.].[MyView] WHERE Column = 'Condition' AND [ClientName] LIKE 'BLA%';


SELECT DISTINCT [XMLClientID]  = STUFF((Select '' +[XMLClientID] 
                           FROM  #ClientNumber
                           FOR XML Path(''),TYPE).value('(./text())[1]','varchar(max)'),1,0,'') 
                                            INTO #TempString
                                            FROM #ClientNumber A
SET @StringClientID = (SELECT [XMLClientID] FROM #TempString)
SET @XMLStringClient = CAST(@StringClientID AS XML);

EXEC dbo.My_StoredProcedure @XMLStringClient