For XML Explicit with many levels of sub-elements

Posted on 2006-06-08
Last Modified: 2013-11-19
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?

Question by:sanw2020
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
LVL 13

Expert Comment

ID: 16865662
LVL 13

Expert Comment

ID: 16865732

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.
LVL 13

Expert Comment

ID: 16865736
And another good article...

I need to write one one of these days... but XML generationis becoming much simpler with 2005.
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.


Author Comment

ID: 16866316
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!
LVL 13

Expert Comment

ID: 16867098
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.

Author Comment

ID: 16869558
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.
LVL 13

Accepted Solution

Atlanta_Mike earned 500 total points
ID: 16870093
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.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
The viewer will learn how to dynamically set the form action using jQuery.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question