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