April 8, 2011 at 4:54 am
I'm retrieving a recordset with dynamic sql, using sp_executesql. The executed query results in a recordset that has a unknown number of columns. Given is that the first column contains the identifier (INT format).
I would like to iterate trough the given recordset, and retrieve each row as XML ('FOR XML RAW'). How can i iterate trough this 'unknown' recordset without a cursor preferably (cursors and xml don't seem to mix/match well), without knowing the identifier?
I currently have the following code in my SP, of course this doesnt work well, as the identifier is not sequential (1,2,3,4 ), can be (2,22,303 etc).
EXEC sp_executesql @inputQuery -- N'SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM Customer'
SELECT @RowCount = @@rowcount
-- Declare iterator
DECLARE @I INT
-- Initialize iterator
SET @I = 1
-- Loop through recordset
WHILE (@I <= @RowCount)
BEGIN
SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM Customer WHERE CustId = @I FOR XML RAW
SET @I = @I + 1
END
sidenote: its retrieved from a unknown source, but this is for internal use (single administrator), security is not an issue here.
April 8, 2011 at 5:09 am
Is there any prob with using a temp table? If not, create a temp table with IDENITITY field and insert data from customer table. Then you can loop through each row on that IDENTITY field.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 8, 2011 at 6:19 am
I dont think i can create a temp table without knowing what columns (number/type) there are on forehand, can i?
April 8, 2011 at 6:28 am
Change @inputQuery to output only first column and insert into temp table with single column
Far away is close at hand in the images of elsewhere.
Anon.
April 8, 2011 at 6:29 am
but you do know the names and number:
SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM Customer
into #tmp WHERE 1=2 FOR XML RAW
Lowell
April 8, 2011 at 6:33 am
That was just an example (therefor in --comment, on the first line). It basically can be anything.
April 8, 2011 at 6:34 am
but the results is always a single column FOR XML isn't it? it doesn't matter if the XML contains multiple nodes or not.
Lowell
April 8, 2011 at 6:38 am
That is correct, so the conclusion is that i should create a two column width temp table
First column IDENTITY,
Second column, containing the XML string
Would that be the right way?
April 8, 2011 at 6:39 am
As per Lowell's query you can create the temp table.
Actually, I tried to ask you to write a script as below.
EXEC sp_executesql @inputQuery -- N'SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM Customer'
SELECT @RowCount = @@rowcount
-- Declare iterator
DECLARE @I INT
-- Initialize iterator
CREATE TABLE #Customer
(
Id INT IDENTITY(1,1),
CustId INT,
Name VARCHAR(300),
Emailadres VARCHAR(300)
)
INSERT INTO #Customer(CustId, Name, Emailadres)
SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM Customer
SET @I = 1
-- Loop through recordset
WHILE (@I <= @RowCount)
BEGIN
SELECT CustId AS label1, Name AS label2,Emailadres AS label3 FROM #Customer WHERE Id = @I FOR XML RAW
SET @I = @I + 1
END
I could not check the code, so you may get syntax error.
_____________________________________________
One ounce of practice is more important than tonnes of dreams
April 8, 2011 at 6:45 am
mvklingeren (4/8/2011)
That is correct, so the conclusion is that i should create a two column width temp tableFirst column IDENTITY,
Second column, containing the XML string
Would that be the right way?
Yep i think that would work:
Create Table #Results(
id int identity(1,1) not null primary key,
ResultsXML XML )
...--loop/cursor?
INSERT INTO #Results(ResultsXML)
select lotsofstuff from SomeTable FORXMLRAW
...
Lowell
April 8, 2011 at 7:11 am
That does not really seem to fit the shoe..
INSERT INTO #Results(ResultsXML)
SELECT * FROM [SomeDB].[dbo].[Customer] WHERE Custid=1 FOR XML PATH('')
Msg 6819, Level 16, State 1, Line 2
The FOR XML clause is not allowed in a INSERT statement.
April 8, 2011 at 7:13 am
Consider using the ROW_NUMBER() function to assign sequential record numbers.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 8, 2011 at 7:34 am
you need to wrap and alias the XML like this:
Create Table #Results(
id int identity(1,1) not null primary key,
ResultsXML XML )
INSERT INTO #Results(ResultsXML)
SELECT (
SELECT * FROM sys.objects WHERE type='U' FOR XML PATH('')
) As MyXML
Lowell
April 8, 2011 at 10:42 am
Alternatively
If you realy can't know the structure of the temp table in advance, allow it to be created dynamically and use the ROW_NUMBER() function to apply an artificial key that you can then itterate over
DECLARE @I INT
SELECT CustId AS label1, Name AS label2,Emailadres AS label3, ROW_NUMBER() OVER(ORDER BY CustId) AS row
INTO #Customer
FROM Customer
DECLARE @RowCount INT = @@Rowcount
SET @I = 1
-- Loop through recordset
WHILE (@I <= @RowCount)
BEGIN
SELECT label1, label2, label3 FROM #Customer WHERE row = @I FOR XML RAW
SET @I = @I + 1
END
DROP TABLE #customer
HTH
Ben
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply