Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2002
    Posts
    74

    Unanswered: Converting SQL Server Data into XML

    Hi Folks!

    I have the following sql script I wrote using Explicit Option to convert data from SQL Server tables into a single XML file. I am aware of the tedious nature of the select statements, but this seems to the only option I have to depict parent-child nature of the data in XML format and also to schedule it as a job to run via SQL Server Agent.

    My problem is that as I run this using the following command, I get "There is insufficient system memory to run this query" error.
    I am using the following commnd:
    exec master..xp_cmdshell 'bcp "EXEC swr_cv2..sproc_BuildXMLTree" queryout "C:\test.xml" -U -P -c -r -t'

    Is there any way I can tune my query to fix that error?

    Thanks so much for your help!

    -Parul
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ParulV
    Hi Folks!

    I am aware of the tedious nature of the select statements,
    -Parul
    Really tedious Parul,For the first time I am seeing so big a proc... rather a Mamoth
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  3. #3
    Join Date
    Oct 2002
    Posts
    74
    I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?

  4. #4
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ParulV
    I know, that's because the XML feed layout is huge as well...is there a way to tune a FOR XML EXPLICIT query?
    You can increase the max server memory,but I am not sure whether that will help you...check this
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  5. #5
    Join Date
    Oct 2002
    Posts
    74
    Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?

  6. #6
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ParulV
    Thanks! I am really hoping not to tamper with the server settings as it is a production database. I am hoping I can may be build a temp table around this data, any ideas?
    have you tried XPath queries in this case ?
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  7. #7
    Join Date
    Oct 2002
    Posts
    74
    No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?

  8. #8
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ParulV
    No, I am not sure if I can write XPath Queries and schedule them via SQL Server Agent. Do you know if that is possible? Or do I need a .net application for it?
    check this ...
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  9. #9
    Join Date
    Oct 2002
    Posts
    74
    Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily....

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by ParulV
    Thanks! This seems helpful in generating an XML layout; however, I am still wondering how I can schedule this to run daily....
    An easy option can be Altova xmlspy 2006.
    Try this www.altova.com and download XMLSPY 2006
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Oct 2002
    Posts
    74
    Thanks! BTW, I got my sql query to work! I replaced all the unions with inserts - now I am inserting all the data into an actual physical data, and then running FOR XML EXPLICIT on it. It seems to be working fine now!

    I had a question though - do you know if there is a sql command to merge two files. Basically, I want to merge the header for the xml file with actual xml data in another file - is that possible without running any DOS commands?

    Thanks so much!

Posting Permissions

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