CODEWITHSUNDEEP
sql-serverxml
V Krishan
V Krishan

Reputation: 153

Concatenate nvarchar column and convert to XML

I am currently fetching an integer row, then converting the row to a Nvarchar in a hash table, then converting this to XML. The aim is to have all values returned in the one XML variable like so:

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

Currently my code returns this as an XML row like so:

 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" />

Here is my code which i have anonimised:

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

Any steer would be great as the stuff will not work due to the for XML.

Upvotes: 0

Views: 240

Answers (3)

V Krishan
V Krishan

Reputation: 153

Thanks to @John Cappelletti for the link. Here is my solution:

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

Upvotes: 0

McNets
McNets

Reputation: 10807

You could use integer values instead of convert and format it.

 CREATE TABLE tbl (id int);

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

 SELECT id
 FROM tbl item
 FOR XML AUTO

Output:

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

dbfiddle here

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37337

Try this:

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('')

Upvotes: 0

Related Questions