For XML Explicit with many levels of sub-elements

I'm not sure what area this should go to -- SQL or XML.  If SQL is not correct, please move this over to XML (or let me know).

I'm trying to send a SQL script to an XML file for this first time.  I'm doing this because it's required by a client, and unfortunately, it looks like this first one's going to be very tough.

I've ruled out using the For XML Raw and For XML Auto commands because neither give me even close to the correct XML structure.  This leaves For XML Eplicit.  I've started trying to build my query to match the specifications, but am totally lost when it comes to creating the many levels that are required.

I've copied in part of the XML document that I have to create.  Specifically, I'm having trouble figuring out how to create the ThisDocumentIdentifier level (level 2 if I understand it correctly) because there are no data fields at that level -- only structural tags.  

            <DateTime DateTimeQualifier="On">2006-02-04T09:15:22Z</DocumentIdentifier>
                  <PartnerName>John Doe Labs</PartnerName>
                  <PartnerIdentifier Agency="D-U-N-S">000000000</PartnerIdentifier>
                  <PartnerName>Jane Smith Customer</PartnerName>
                  <PartnerIdentifier Agency="D-U-N-S">000000000</PartnerIdentifier>
            <PurchaseOrderTypeCode Domain="ANSI-ASC-X12-92">SA</PurchaseOrderTypeCode>
                  <DateTime DateTimeQualifier="On">2006-02-04T00:00:00Z</DateTime>
            <LanguageCode Domain="ISO-639-2T">ENG</LanguageCode>
            <CurrencyCode Domain="ISO-4217">USD</CurrencyCode>
            <SpecialInstructions InstructionType="General">Z005: Validate pricing</SpecialInstructions>
            <SpecialInstructions InstructionType="General">Have a nice day!</SpecialInstructions>
            <SpecialInstructions InstructionType="General">See you later!</SpecialInstructions>

When I tried to refer to all fields at the Header level as 1, and all fields at the ThisDocumentIdentifier level as 2, I get the error "XML tag ID 20 that was originally declared as 'ThisDocumentIdentifier' is being redeclared as 'ThisDocumentDateTime'."

I've also copied in my first lame attempt.  This is of course not complete and doesn't yet use any data fields -- only constants and identifiers, but if I can't get this part rigtht I won't get any of the rest of it right.  

select 10 as Tag, NULL as Parent,
      NULL as [Header!10!ThisDocumentIdentifier!element],
      NULL as [Header!10!ThisDocumentDateTime!element],
      NULL as [Header!10!From!element],
      NULL as [Header!10!To!element],
            @DocID as [ThisDocumentIdentifier!20!DocumentIdentifier],
            @ExportDTZ as [ThisDocumentDateTime!20!DateTime],
            NULL as [From!20!PartnerInformation!element],
                  @FromName as [PartnerInformation!30!PartnerName],
                  @FromID as [PartnerInformation!30!PartnerIdentifier],
            NULL as [To!20!PartnerInformation!element],
                  @ToName as [PartnerInformation!30!PartnerName],
                  @ToID as [PartnerInformation!30!PartnerIdentifier]

I've looked through the articles on EE and have found some very helpful ones, but none of the examples deal with an XML document that is so complicated (at least it seems to me).  

Can anyone help me?

Sandy WestmanIntegrations ConsultantAsked:
Who is Participating?
Atlanta_MikeConnect With a Mentor Commented:
There is another way... if you have small data involves using a temp table and populating the temp table to duplicate the resultset that you are building with the unions.

If you comment out the FOR XML EXPLICIT line, it will return a result set that you can get an idea of what it is doing... If you can duplicate that...then you have it made.

Then all you have to do is insert into the table the record you need for each level.

The key is in ordering using the id from the table to keep the hierarchy correct. Use the Hide option so it doesn't show up in the XML.

I have tons of experience using XML explicit so see what you can do and let me know as you step through it and I'll do my best to respond quickly.
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

And another good article...

I need to write one one of these days... but XML generationis becoming much simpler with 2005.
Sandy WestmanIntegrations ConsultantAuthor Commented:
Thanks for your quick response.  I had seen the DeMystify article but obviously didn't look at it closely enough.  

1. With that information, I have been able to generate 10 lines of my XML document.  The downside is that it took 40 lines of SQL code including 3 select and 2 union statements.  Either I'm doing it wrong or there must be a better way!  Please tell me there's an easier way.  

Here's what I was able to produce:
<?xml version="1.0" encoding="UTF-8" ?>
- <OrderCreate xmlns:sql="urn:schemas-microsoft-com:xml-sql" xmlns:xsi="" xsi:SchemaLocation="OrderCreate.xsd">
- <Header>
- <ThisDocumentIdentifier>
- <ThisDocumentDateTime>

Here's the SQL that produced it:
      DocumentID uniqueidentifier,
      DocDateTime varchar(20))

INSERT INTO #HEADER (DocumentID, DocDateTime)

SELECT      1      as Tag,
      NULL       as Parent,
      NULL      as [Header!1!ThisDocumentIdentifer!element],
      NULL      as [Header!1!ThisDocumentDateTime!element],
      NULL      as [Header!1!From!element],
      NULL      as [Header!1!To!element],
      NULL      as [ThisDocumentIdentifier!2!DocumentIdentifier!element],
      NULL      as [ThisDocumentDateTime!3!DateTime!element]
FROM #HEADER as Header
SELECT      2      as tag,
      1       as parent,
      ThisDocumentIdentifier.DocumentID as DocumentIdentifier,
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
      ON Header.DocumentID = ThisDocumentIdentifier.DocumentID
SELECT      3      as tag,
      1      as parent,
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
      ON Header.DocumentID = ThisDocumentIdentifier.DocumentID
      JOIN #Header ThisDocumentDateTime
      ON Header.DocumentID = ThisDocumentDateTime.DocumentID

2. Let me ask you this.  I have the .XSD file that my XML document has to conform to.  I was reading about XML Templates earlier in the week and thought that I'd be able to leverage the .XSD file to format my XML document by calling my stored procedure from within a template that references the .XSD.  But I must have expected tooo much because all it seems to add to the document are the header lines.  It seems I have to label my columns with names that match the XSD file, and I would have to do that anyway to get a correct schema.  Is there something I'm missing there?  

3. Finally, while it's not what the client wanted, I could just have a .NET console application we are writing to send the XML file to the vendor's website transform the .XSD into a class and have the app do all of the data work.  The client wanted to separate the data work from the file transmission so that if they start using other vendors they won't have to change .NET code, just the stored procedure.  Will it just be too much work on the SQL side to justify doing it with XML Explicit if I can do it with the .NET app?

Thanks for all of your expert knowledge!
Nope, you're doing it right... thats the downside of using XML EXPLCIT... I have some that have over a thousand lines of code.

I thing you can reference the xsd in SQL Server 2005! I need to read more about the 2005 functionality.

That's a very good question and something we've thought about also. I it is a lot of work on the database side in SQL Server 2000. Once you get the hang of it, it will go pretty smoothly. Especially if they're as small as your example is.

Good luck! And let me know how it is going.
Sandy WestmanIntegrations ConsultantAuthor Commented:
Thank you so much for your help.  I may still try to find another way just because I think this syntax will be very hard for the client to understand and possibly modify for other vendors.  The document I have to create has about 100 lines in it -- about 40 data lines and the rest "structure."  When I think about what it took to create 8 lines, I shiver thinking about creating the rest.  I might try to get the vendor to simplify the XSD they're using since I think my client is the first to send documents to this schema that they adopted.  Or I might see if we can generate the XML out of the .NET code.  But you got me past being stuck and that's a huge contribution!  

As long as I have you on the line...I have another question.  Many fields within the XML document have to include qualifiers -- like the one I've shown below.  I made a pass at trying to include the qualifier but so far haven't been able to get the syntax right.  

This is how it looks now:

This is how it's supposed to look:
  <DateTime DateTimeQualifier="On">2006-02-04T09:15:22Z</DateTime>

I've been able to get as far as:
  <DateTime>DateTimeQualifier="On" 2006-02-04T09:15:22Z</DateTime>

Could you show me how I would need to change the sql code to make this line happen?  

Thanks again.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.