none
Convert VARCHAR to XML in SQL Server 2008

    Question

  • Hi Experts,

    Does anybody know, "How to convert VARCHAR to XML" ?

    I need to return XML from Stored Procedure (SP).

    Note : I am using SQL Server 2008 and doing this using LINQ.

    Sample code would be greatly appreciated.

    Thanks.




    Friday, July 24, 2009 7:53 AM

Answers

  • Vijay,

    You cant assign @sql to the xml variable because @sql contains the sql statement to create the xml, it does not contain valid xml until executed.  You should use an output parameter as Peso has suggested.  Please note that Peso forgot to define the dynamic variable as an output variable, but other than that his example was on the money.

    Here is a working sample.

    DECLARE @sql NVARCHAR(MAX),
    		@xml_result XML
    
    IF OBJECT_ID('tempdb..#t') IS NOT NULL
    BEGIN
    	DROP TABLE #t;
    END
    
    CREATE TABLE #t(
    ProductId INT,
    ProductName VARCHAR(10)
    );
    
    INSERT INTO #t VALUES (1,'Product1');
    INSERT INTO #t VALUES (2,'Product2');
    
    SET @sql = N'set @x = (select ProductId, ProductName from #t for xml path(''Product''),root(''Products''))'
    
    PRINT @sql
    EXEC sp_executesql @sql, N'@x xml output',@x=@xml_result out
    
    SELECT @xml_result
    

    http://jahaines.blogspot.com/
    Friday, July 24, 2009 2:19 PM
  • In order for sp_executesql to receive an output, you must assign the SELECT to a variable.
    What you need is something similar to this


    declare @result xml

    set @sql = 'set @myxml = (select ... for xml path, root xsinil'

    sp_executesql @sql, '@myxml XML', @myxml = @result out
    • Marked as answer by Vijay Jadhav Friday, July 24, 2009 3:09 PM
    Friday, July 24, 2009 11:25 AM
  • Hi Guys,

    Really Gr8!

    We have done it in following manner : (Look at Bold Lines)

    ALTER PROCEDURE dbo.[usp ATI SearchStudent V1]
        @AcademicDetails NVARCHAR(50),
        @FirstName NVARCHAR(50),
        @MiddleName NVARCHAR(50),
        @LastName NVARCHAR(50),
        @Result XML OUTPUT
    AS

    BEGIN 

    --DECLARE @sSQL VARCHAR(8000)
    DECLARE @sSQL NVARCHAR(MAX)
    DECLARE @sCondition NVARCHAR(20)
    DECLARE @sql NVARCHAR(MAX)

    SET @sCondition = ' WHERE '

    SET @sSQL = '(SELECT StudentID,
                    AcademicDetails,
                    FirstName as "PersonalDetails/FirstName",
                    MiddleName as "PersonalDetails/MiddleName",
                    LastName as "PersonalDetails/LastName" FROM tb_StudentInfo'

    IF(@AcademicDetails <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' AcademicDetails = ''' +  @AcademicDetails + ''''
        SET  @sCondition = ' AND '
    END

    IF(@FirstName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' FirstName = ''' +  @FirstName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@MiddleName <>  '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' MiddleName = ''' +  @MiddleName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@LastName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' LastName = ''' +  @LastName + ''''
        SET  @sCondition = ' AND '
    END

    SET @sSQL = @sSQL + ' FOR XML PATH(''Student''), ROOT(''Sis''), ELEMENTS XSINIL)'

    SET @sql = N'set @x = ' +  @sSQL

    PRINT @sql

    EXEC sp_executesql @sql, N'@x xml output',@x = @Result out


    END

    And this worked like a charm for me.

    Thanks to both of you.
    • Edited by Vijay Jadhav Friday, July 24, 2009 3:20 PM
    • Marked as answer by Vijay Jadhav Friday, September 04, 2009 2:28 PM
    Friday, July 24, 2009 3:08 PM

All replies

  • You can assign a well-formed varchar string to an XML variable and SQL Server. SQL Server will perform the conversion automatically. if you want to convert explicitly, you can use CAST function.

    It looks like you are generating an varchar string (with xml structure) and trying to convert it to XML. If that is the case, I would suggest using FOR XML, that can directly generate XML output.
    Beyond Relational
    Friday, July 24, 2009 9:03 AM
  • Hi Jacob,

    Thanks for your reply.

    I have generate dynamic sql and I have assigned that result to VARCHAR. Finally, I need to assign VARCHAR to XML. Because return type is XML. I have used FOR XML.

    Here is my SP:

    ALTER PROCEDURE dbo.[usp ATI SearchStudent]
        @AcademicDetails NVARCHAR(20),
        @FirstName NVARCHAR(20),
        @MiddleName NVARCHAR(20),
        @LastName NVARCHAR(20),
        @Result XML OUTPUT
    AS

    BEGIN

    DECLARE @sSQL VARCHAR(8000)
    DECLARE @sCondition NVARCHAR(20)

    SET @sCondition = ' WHERE '

    SET @sSQL = '(SELECT StudentID,
                    AcademicDetails,
                    FirstName as "PersonalDetails/FirstName",
                    MiddleName as "PersonalDetails/MiddleName",
                    LastName as "PersonalDetails/LastName" FROM tb_StudentInfo'

    IF(@AcademicDetails <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' AcademicDetails = ''' +  @AcademicDetails + ''''
        SET  @sCondition = ' AND '
    END

    IF(@FirstName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' FirstName = ''' +  @FirstName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@MiddleName <>  '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' MiddleName = ''' +  @MiddleName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@LastName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' LastName = ''' +  @LastName + ''''
        SET  @sCondition = ' AND '
    END

    SET @sSQL = @sSQL + ' FOR XML PATH(''Student''), ROOT(''Sis''), ELEMENTS XSINIL);'

    SET @Result = @sSQL

    --EXEC SP_EXECUTESQL @Result

    END

    The above SP gives me error as : "Data at the root level is invalid. Line 1, position 1", Why ?

    I think the problem lies in SET @Result = @sSQL. Because @Result is of XML type, where @sSQL is of NVARCHAR.

    That's why I asked question for conversion.

    But my another SP work really well. SP contents are :

    ALTER PROCEDURE dbo.[usp ATI SearchStudent]
        @AcademicDetails NVARCHAR(20),
        @Action NVARCHAR(20),
        @UserName NVARCHAR(20),
        @PassWord NVARCHAR(20),
        @FirstName NVARCHAR(20),
        @MiddleName NVARCHAR(20),
        @LastName NVARCHAR(20),
        @Result XML OUTPUT
    AS
    BEGIN 
    SET @Result = (SELECT  StudentID,
                    AcademicDetails,
                    Action,
                    UserName AS "Authentication/UserName",
                    PassWord AS "Authentication/Password",
                    FirstName AS "PersonalDetails/FirstName",
                    MiddleName AS "PersonalDetails/MiddleName",
                    LastName AS "PersonalDetails/LastName"
                    FROM tb_StudentInfo WHERE FirstName = @FirstName
    FOR XML PATH('Student'), ROOT('Sis'), ELEMENTS XSINIL);
    END

    Also, I have used CAST function, but didn't work.

    Any solution Jacob ?


    • Edited by Vijay Jadhav Friday, July 24, 2009 11:35 AM
    • Marked as answer by Vijay Jadhav Friday, September 04, 2009 2:28 PM
    • Unmarked as answer by Vijay Jadhav Friday, September 04, 2009 2:28 PM
    Friday, July 24, 2009 11:20 AM
  • In order for sp_executesql to receive an output, you must assign the SELECT to a variable.
    What you need is something similar to this


    declare @result xml

    set @sql = 'set @myxml = (select ... for xml path, root xsinil'

    sp_executesql @sql, '@myxml XML', @myxml = @result out
    • Marked as answer by Vijay Jadhav Friday, July 24, 2009 3:09 PM
    Friday, July 24, 2009 11:25 AM
  • Hi Peso,

    Thanks for reply.

    Will you explain it in detail ?

    I am not getting you.

    Thanks.


    Friday, July 24, 2009 1:24 PM
  • Hi Peso,

    As you mentioned, I have done it in following way :

    EXEC SP_EXECUTESQL @sSQL

    SET @result = @sSQL

    But, gives error as :  Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
    Friday, July 24, 2009 1:48 PM
  • XML conversion should be done in the application layer and not in the database.
    Friday, July 24, 2009 1:49 PM
  • Vijay,

    You cant assign @sql to the xml variable because @sql contains the sql statement to create the xml, it does not contain valid xml until executed.  You should use an output parameter as Peso has suggested.  Please note that Peso forgot to define the dynamic variable as an output variable, but other than that his example was on the money.

    Here is a working sample.

    DECLARE @sql NVARCHAR(MAX),
    		@xml_result XML
    
    IF OBJECT_ID('tempdb..#t') IS NOT NULL
    BEGIN
    	DROP TABLE #t;
    END
    
    CREATE TABLE #t(
    ProductId INT,
    ProductName VARCHAR(10)
    );
    
    INSERT INTO #t VALUES (1,'Product1');
    INSERT INTO #t VALUES (2,'Product2');
    
    SET @sql = N'set @x = (select ProductId, ProductName from #t for xml path(''Product''),root(''Products''))'
    
    PRINT @sql
    EXEC sp_executesql @sql, N'@x xml output',@x=@xml_result out
    
    SELECT @xml_result
    

    http://jahaines.blogspot.com/
    Friday, July 24, 2009 2:19 PM
  • Not only that, I now see I forgot a right paranthesis too in my pseudocode!
    Friday, July 24, 2009 2:42 PM
  • Not only that, I now see I forgot a right paranthesis too in my pseudocode!

    Lol, I even missed that one :-). 
    http://jahaines.blogspot.com/
    Friday, July 24, 2009 2:45 PM
  • Hi Guys,

    Not getting both of you.

    What would be the sample SP in my case ?

    Thanks.
    Friday, July 24, 2009 2:52 PM
  • Hi Guys,

    Really Gr8!

    We have done it in following manner : (Look at Bold Lines)

    ALTER PROCEDURE dbo.[usp ATI SearchStudent V1]
        @AcademicDetails NVARCHAR(50),
        @FirstName NVARCHAR(50),
        @MiddleName NVARCHAR(50),
        @LastName NVARCHAR(50),
        @Result XML OUTPUT
    AS

    BEGIN 

    --DECLARE @sSQL VARCHAR(8000)
    DECLARE @sSQL NVARCHAR(MAX)
    DECLARE @sCondition NVARCHAR(20)
    DECLARE @sql NVARCHAR(MAX)

    SET @sCondition = ' WHERE '

    SET @sSQL = '(SELECT StudentID,
                    AcademicDetails,
                    FirstName as "PersonalDetails/FirstName",
                    MiddleName as "PersonalDetails/MiddleName",
                    LastName as "PersonalDetails/LastName" FROM tb_StudentInfo'

    IF(@AcademicDetails <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' AcademicDetails = ''' +  @AcademicDetails + ''''
        SET  @sCondition = ' AND '
    END

    IF(@FirstName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' FirstName = ''' +  @FirstName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@MiddleName <>  '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' MiddleName = ''' +  @MiddleName + ''''
        SET  @sCondition = ' AND '
    END

    IF(@LastName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' LastName = ''' +  @LastName + ''''
        SET  @sCondition = ' AND '
    END

    SET @sSQL = @sSQL + ' FOR XML PATH(''Student''), ROOT(''Sis''), ELEMENTS XSINIL)'

    SET @sql = N'set @x = ' +  @sSQL

    PRINT @sql

    EXEC sp_executesql @sql, N'@x xml output',@x = @Result out


    END

    And this worked like a charm for me.

    Thanks to both of you.
    • Edited by Vijay Jadhav Friday, July 24, 2009 3:20 PM
    • Marked as answer by Vijay Jadhav Friday, September 04, 2009 2:28 PM
    Friday, July 24, 2009 3:08 PM
  • Vijay,

    You have to modify the line below.  At present you are trying to assign @result to @sql, which is the actually sql statement not the results of the sql statement.  You need to remove this variable assignment and use an output clause.  Here is a sample.

    ALTER PROCEDURE dbo.[usp ATI SearchStudent]
        @AcademicDetails NVARCHAR(20),
        @FirstName NVARCHAR(20),
        @MiddleName NVARCHAR(20),
        @LastName NVARCHAR(20),
        @Result XML OUTPUT
    AS
    
    BEGIN 
    
    DECLARE @sSQL VARCHAR(8000)
    DECLARE @sCondition NVARCHAR(20)
    
    SET @sCondition = ' WHERE '
    
    -- ADD THE VARIABLE ASSIGNMENT HERE --<---------------------------------------- modified
    SET @sSQL = 'set @x = (SELECT StudentID,
                    AcademicDetails,
                    FirstName as "PersonalDetails/FirstName",
                    MiddleName as "PersonalDetails/MiddleName",
                    LastName as "PersonalDetails/LastName" FROM tb_StudentInfo'
    
    IF(@AcademicDetails <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' AcademicDetails = ''' +  @AcademicDetails + ''''
        SET  @sCondition = ' AND '
    END
    
    IF(@FirstName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' FirstName = ''' +  @FirstName + ''''
        SET  @sCondition = ' AND '
    END
    
    IF(@MiddleName <>  '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' MiddleName = ''' +  @MiddleName + ''''
        SET  @sCondition = ' AND '
    END
    
    IF(@LastName <> '')
    BEGIN
        SET @sSQL = @sSQL + @sCondition + ' LastName = ''' +  @LastName + ''''
        SET  @sCondition = ' AND '
    END
    
    SET @sSQL = @sSQL + ' FOR XML PATH(''Student''), ROOT(''Sis''), ELEMENTS XSINIL);'
    
    --SET @Result = @sSQL --<----------------------------------------comment this out
    
    EXEC SP_EXECUTESQL @sSQL, '@x xml output',@x=@result OUT --<----------------------------------------modified
    
    SELECT @result
    
    END
    GO
    

    http://jahaines.blogspot.com/
    Friday, July 24, 2009 3:08 PM
  • Hi Adam,

    Gr8 man! Your specified SP also works.

    Thank you very much Peso and Adam. 

    Thanks.
    Friday, July 24, 2009 3:17 PM
  • Hi Guys,

    Could one of you explain what we have done actually ?

    Thanks.
    Friday, July 24, 2009 3:27 PM
  • Vijay,

    sp_executesql allows dynamic sql parameterization.  We utilized this by creating an output variable called @x, of type XML. @x is a variable that we can use inline with the dynamic sql. In the first part of the dynamic sql we had to assign the dynamic sql variable @x to the select statement. Essentially, this is no different than doing something like: set @var = (select mycolumn from mytable).  Once we had all the variables set, we assigned the dynamic sql variable (@x) to the batch scoped variable (@result).

    You should have a look at sp_executesql in BOL: http://msdn.microsoft.com/en-us/library/ms188001.aspx
    http://jahaines.blogspot.com/
    Friday, July 24, 2009 3:53 PM
  • Vijay,

    I responded to your question a moment ago, but it seems the post never made its way to the forum.  Anyway I will post it again, but dont be surprised if the other post automagically appears :).

    <<Could one of you explain what we have done actually ?

    Sp_executesql has native functionality to support variables within the scope of dynamic sql.  We utilizied this by creating a variable called @x and used this in simple variable assignment.  Essentially the dynamic sql is doing something similar to this set @x = (select .... from mytable).  We then assigned the batch scoped variable @results to the value of @x, which is the result of the dynamic sql execution. 

    You can read more about this in BOL:
    http://msdn.microsoft.com/en-us/library/ms188001.aspx


    http://jahaines.blogspot.com/
    Friday, July 24, 2009 4:33 PM
  • Hi Adam,

    Thanks for details.

    Thanks.
    Monday, July 27, 2009 5:33 AM