Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36

    Thumbs down Unanswered: Problem getting select data set for XML Auto, Elements into a table variable

    I have a simple select quesry but with 'for XML AUTO, ELEMENTS'. I want to put in the resulting xml string into a temporary table and then alter that string as per my requirements. But I am unable to put this XML string into a table variable. Please offer your suggestions.
    Last edited by suresh_m_kumar; 05-26-04 at 12:19. Reason: XML, Auto replace by XML Auto. A typing error.

  2. #2
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36

    Question

    If I put it like

    select @l_variable = (select my sql statement for Auto, Elements)
    it gives me syntax error.

    I can't do a select into a temp table from my select statement for XML Auto.

    I can't use select into as well.

    I can't enclose my entire select statment in paranthesis to put it in a table variable.

    How do I proceed?

  3. #3
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    Here is the actual query. I am trying to get the result of the select statement into the table variable:

    declare @i_Customer_Id int
    ,@i_Role_ID int
    ,@i_Base_URL varchar(255)

    declare @l_XML_Table table (XML_String varchar (8000))

    select @i_Customer_Id = 10
    ,@i_Role_ID = 1
    ,@i_Base_URL = 'http://www.NewWebSite.com'

    select
    MM.Module_Description Topic_Type
    ,PM.Page_Description Title
    ,@i_Base_URL + PM.Page_URL URL
    from
    Role_Page_Map RPM
    ,Module_Master MM
    ,Page_Master PM

    where RPM.Role_ID = @i_Role_ID
    and RPM.Customer_Id = @i_Customer_Id

    and RPM.Page_ID = PM.Page_ID
    and PM.Module_ID = MM.Module_ID

    for XML Auto, Elements

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    You can't select FOR XML into a table. See BOL for more information.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  5. #5
    Join Date
    Sep 2003
    Location
    Mumbai, India
    Posts
    36
    That's true. However can't the resultant xml string be taken in a varchar variable as well?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •