Solved

For XML Explicit with many levels of sub-elements

Posted on 2006-06-08
7
896 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
0
Comment
Question by:sanw2020
  • 5
  • 2
7 Comments
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16865662
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
ID: 16865732
And

http://www.quest-pipelines.com/newsletter-v3/0502_B.htm

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

Expert Comment

by:Atlanta_Mike
ID: 16865736
And another good article...

http://www.sqlmag.com/Article/ArticleID/19810/sql_server_19810.html

I need to write one one of these days... but XML generationis becoming much simpler with 2005.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:sanw2020
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="http://www.w3.org/2001/XMLSchema-instance" xsi:SchemaLocation="OrderCreate.xsd">
- <Header>
- <ThisDocumentIdentifier>
  <DocumentIdentifier>584D43F2-814F-40D3-9091-7BE927F8C281</DocumentIdentifier>
  </ThisDocumentIdentifier>
- <ThisDocumentDateTime>
  <DateTime>2006-10-11T08:20:30Z</DateTime>
  </ThisDocumentDateTime>
  </Header>
  </OrderCreate>

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

INSERT INTO #HEADER (DocumentID, DocDateTime)
SELECT
      NewID(),
      '2006-10-11T08:20:30Z'

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
UNION
SELECT      2      as tag,
      1       as parent,
      NULL,
      NULL,
      NULL,
      NULL,
      ThisDocumentIdentifier.DocumentID as DocumentIdentifier,
      NULL
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
      ON Header.DocumentID = ThisDocumentIdentifier.DocumentID
UNION
SELECT      3      as tag,
      1      as parent,
      NULL,
      NULL,
      NULL,
      NULL,
      ThisDocumentIdentifier.DocumentID,
      ThisDocumentDateTime.DocDateTime
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
      ON Header.DocumentID = ThisDocumentIdentifier.DocumentID
      JOIN #Header ThisDocumentDateTime
      ON Header.DocumentID = ThisDocumentDateTime.DocumentID
ORDER BY 3
FOR XML EXPLICIT

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!
0
 
LVL 13

Expert Comment

by:Atlanta_Mike
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.
0
 

Author Comment

by:sanw2020
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:
 <ThisDocumentDateTime>
  <DateTime>2006-10-11T08:20:30Z</DateTime>
  </ThisDocumentDateTime>

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

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

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

Thanks again.
0
 
LVL 13

Accepted Solution

by:
Atlanta_Mike earned 500 total points
ID: 16870093
There is another way... if you have small data sets...it 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.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now