Post reply

so close with FOR XML EXPLICIT!

  • November 20, 2007 at 2:54 pm

    #103433

    Hello,

    If there is anyone experienced with FOR XML EXPLICT that would like to look at my query I would be very grateful. I am 99% of the way to getting my XML the way I need it but am stuck on one child element.:crazy:

    Thanks!

    Jonatrhan

  • November 21, 2007 at 5:10 am

    #753047

    Since I know just how HORRIFIC the XML EXPLICIT statements can be, I shudder to say this, but go ahead and post the query (maybe as an attachment) and someone will look at it.

    Just a question, since you're working SQL Server 2005, why not use XML PATH? It's one hell of a lot easier to work with than XML EXPLICIT (UNION...UNION...UNION...UNION blech).

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 21, 2007 at 6:38 am

    #753072

    Actually I am working with 2000.

    I have attached "problem.txt" that has my xml as it is, xml that I need to get to and my query.

    Thanks very much for any help!

    Jonathan

  • November 21, 2007 at 7:54 am

    #753112

    First thing I noticed is that it looks like you need to add another element here:

    NULL AS [Profile!3!EVPersonalFunds!element],

    NULL AS [SiteOfActivity!4!siteofactivityname!element],

    You want it to be something like this (and of course, you'll have to change all the down stream code):

    NULL AS [Profile!3!EVPersonalFunds!element],

    NULL AS [SitesOfActivity!4!SitesOfActivity!element],

    NULL AS [SiteOfActivity!5!siteofactivityname!element],

    There's probably other stuff in there too.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • November 21, 2007 at 8:12 am

    #753123

    Thanks. Working on it now ...

  • November 21, 2007 at 9:09 am

    #753150

    Got it! But I don't understand it! I hope I never have to write another FOR XML EXPLICT again.

    I have attached what works (Followup.txt). I found I had to include at least one of the SiteOfActivity column names in the SELECT for SitesOfActivity (the one that starts

    SELECT

    4,

    3,

    even though this section returns no data. Otherwise I get a "Tag 4 is not among the open tags ..."

    I have included them all here.

    I have no idea why this works. Life is too short.

    thanks!

    Jonathan

Viewing 6 posts - 1 through 5 (of 5 total)

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