Post reply

How do you create a XML (string) from temporary table in T_SQL

  • December 19, 2005 at 3:13 am

    #188684

     Using SQL Server 2000

    I have written a stored procedure which has a temporary table holding information that I want to extract and store as composite XML in a single column in a new row of another table in the same database i.e. as a string representation of the XML

    I can't seem to grasp the mechanism to use here.  The temporary table can be declared as SELECT ..... FOR XML RAW but it will only give me back row by row data, not a single string.

    Any thoughts on how this can be done?  I'm sure there must be a way.

    Regards

    Tony B

  • December 22, 2005 at 8:00 am

    This was removed by the editor as SPAM

  • December 23, 2005 at 10:53 am

    #611344

    it pretty much cannot be done.

    the FOR XML returns a stream to a dataset or recordset, adn not an standard object in SQL server, so you cannot capture the data, ie:

    select distinct name

    into #temp

     from sysobjects

    for xml raw

    Server: Msg 6819, Level 16, State 1, Line 4

    The FOR XML clause is not allowed in a SELECT INTO statement.

     

    i think you'd have to create your own functions to create the xml, and maybe another to concatenate it all into one varchar(8000) or textfield;

    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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply