Iterating trough a recordset, with a unknown nr. columns / and a identifier that is non sequential (1,2,3,4..)

  • 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.

  • 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

  • I dont think i can create a temp table without knowing what columns (number/type) there are on forehand, can i?

  • 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.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • That was just an example (therefor in --comment, on the first line). It basically can be anything.

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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?

  • 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

  • mvklingeren (4/8/2011)


    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?

    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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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.

  • Consider using the ROW_NUMBER() function to assign sequential record numbers.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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