We help IT Professionals succeed at work.
Get Started

For XML Explicit with many levels of sub-elements

1,046 Views
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.  

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

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]
FOR XML EXPLICIT

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?

Thanks
Sandy
Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 7 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE