Post reply

Query output to an XML file

  • February 5, 2009 at 6:15 am

    #218241

    I am creating an xml file that is being stored on our Web Server by a query using FOR XML EXPLICIT, sp_MakeWebTask and a template (.tpl) file. This XML file is for Google Maps API.

    I'm not sure this is the best way to do this but I stumbled upon it reading here. So far it's worked only part of the time. I think there is a better way just not sure what it is. I'm about ready to build the formatting myself but I'd really like to utilize SQL's XML features.

    When I run my query with FOR XML EXPLICIT and I view the XML in SQL Managment Studio it looks like (Replace "Close Tag with a Greater Than Caret. The carets and everything between them won't show on this forum if I include both):

    <marker lat="12.555" lng="-25.333" "Close Tag"

    <marker lat="15.678" lng="-33.264" "Close Tag"

    However the final product should look like:

    <markers"Close Tag"

    <marker lat="12.555" lng="-25.333" "Close Tag"

    <marker lat="15.678" lng="-33.264" "Close Tag"

    </markers"Close Tag"

    I found a way to create the XML file with the tags by using a .tpl file that includes:

    <markers"Close Tag"

    <%BeginInsert%"Close Tag"

    <%InsertData%"Close Tag"

    <%EndInsert%"Close Tag"

    </markers"Close Tag"

    I have created several xml files that format fine with this .tpl file and sp_MakeWebTask however

    there are many that don't format correctly. There seems to be added whitespace or it breaks up the tags in the middle of it.

    Any suggestions?

  • February 7, 2009 at 3:57 pm

    #940073

    can't see anything....

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • February 7, 2009 at 6:05 pm

    #940081

    hmmm... I just tried editing that post and it seems the website won't allow XML tags. I typed it out and edited the post but nothing shows.

    I'll try it another way.

  • February 7, 2009 at 6:20 pm

    #940084

    Fixed.

    AIf you include both carets it wipes out them and everything between them.

    I used "Closed Tag" to represent a ">"

  • February 7, 2009 at 7:51 pm

    #940091

    Okay... I see it now.

    What version of Sql Server are you running on?

    Will it let you use the ROOT option with your FOR XML? EX: ROOT('markers')

    As for the ones that won't format, you may have to scrub characters that give XML fits.

    Here's an example:

    -- replace special XML characters that cause issues in SQL

    set @CSV = replace(replace(@CSV,'&', '&'),'<', '<')

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • February 8, 2009 at 3:43 am

    #940126

    hi

    i think you can use xml datatype for ommiting the extra spaces on the document

    [font="Courier New"]Aram Koukia: http://www.koukia.ca[/font]

  • February 8, 2009 at 8:27 am

    #940153

    Bob,

    Thanks for the reply.

    I am using SQL 2000.

    I am not sure about using ROOT with the FOR XML command. I didn't know that was an option... hopefully it's applicable in 2000.

  • February 8, 2009 at 8:30 am

    #940154

    aram,

    Are you referring to using the XML Datatype with the sp_MakeWebTask proc?

    I only specified -C (Chacter) with that proc so maybe that will work.

Viewing 8 posts - 1 through 7 (of 7 total)

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