WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
For XML Explicit with many levels of sub-elements
Posted on 2006-06-08
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
</ThisDocumentIdentifer>
<ThisDocumentDateTime>
<DateTime DateTimeQualifier="On">200
</ThisDocumentDateTime>
<From>
<PartnerInformation>
<PartnerName>John Doe Labs</PartnerName>
<PartnerIdentifier Agency="D-U-N-S">000000000
</PartnerInformation>
</From>
<To>
<PartnerInformation>
<PartnerName>Jane Smith Customer</PartnerName>
<PartnerIdentifier Agency="D-U-N-S">000000000
</PartnerInformation>
</To>
</Header>
<OrderCreateBody>
<OrderCreateProperties>
<PurchaseOrderNumber>
<DocumentIdentifier>301706
</PurchaseOrderNumber>
<PurchaseOrderTypeCode Domain="ANSI-ASC-X12-92">S
<PurchaseOrderIssuedDate>
<DateTime DateTimeQualifier="On">200
</PurchaseOrderIssuedDate>
<LanguageCode Domain="ISO-639-2T">ENG</L
<CurrencyCode Domain="ISO-4217">USD</Cur
<BuyerSequenceNumber>0</Bu
<SpecialInstructions InstructionType="General">
<SpecialInstructions InstructionType="General">
<SpecialInstructions InstructionType="General">
</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!ThisDocumentIde
NULL as [Header!10!ThisDocumentDat
NULL as [Header!10!From!element],
NULL as [Header!10!To!element],
@DocID as [ThisDocumentIdentifier!20
@ExportDTZ as [ThisDocumentDateTime!20!D
NULL as [From!20!PartnerInformatio
@FromName as [PartnerInformation!30!Par
@FromID as [PartnerInformation!30!Par
NULL as [To!20!PartnerInformation!
@ToName as [PartnerInformation!30!Par
@ToID as [PartnerInformation!30!Par
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
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
</ThisDocumentIdentifer>
<ThisDocumentDateTime>
<DateTime DateTimeQualifier="On">200
</ThisDocumentDateTime>
<From>
<PartnerInformation>
<PartnerName>John Doe Labs</PartnerName>
<PartnerIdentifier Agency="D-U-N-S">000000000
</PartnerInformation>
</From>
<To>
<PartnerInformation>
<PartnerName>Jane Smith Customer</PartnerName>
<PartnerIdentifier Agency="D-U-N-S">000000000
</PartnerInformation>
</To>
</Header>
<OrderCreateBody>
<OrderCreateProperties>
<PurchaseOrderNumber>
<DocumentIdentifier>301706
</PurchaseOrderNumber>
<PurchaseOrderTypeCode Domain="ANSI-ASC-X12-92">S
<PurchaseOrderIssuedDate>
<DateTime DateTimeQualifier="On">200
</PurchaseOrderIssuedDate>
<LanguageCode Domain="ISO-639-2T">ENG</L
<CurrencyCode Domain="ISO-4217">USD</Cur
<BuyerSequenceNumber>0</Bu
<SpecialInstructions InstructionType="General">
<SpecialInstructions InstructionType="General">
<SpecialInstructions InstructionType="General">
</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!ThisDocumentIde
NULL as [Header!10!ThisDocumentDat
NULL as [Header!10!From!element],
NULL as [Header!10!To!element],
@DocID as [ThisDocumentIdentifier!20
@ExportDTZ as [ThisDocumentDateTime!20!D
NULL as [From!20!PartnerInformatio
@FromName as [PartnerInformation!30!Par
@FromID as [PartnerInformation!30!Par
NULL as [To!20!PartnerInformation!
@ToName as [PartnerInformation!30!Par
@ToID as [PartnerInformation!30!Par
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
[X]
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
7 Comments
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.
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.
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.
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.
Active 1 day ago
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-mic
- <Header>
- <ThisDocumentIdentifier>
<DocumentIdentifier>584D43
</ThisDocumentIdentifier>
- <ThisDocumentDateTime>
<DateTime>2006-10-11T08:20
</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!ThisDocumentIden
NULL as [Header!1!ThisDocumentDate
NULL as [Header!1!From!element],
NULL as [Header!1!To!element],
NULL as [ThisDocumentIdentifier!2!
NULL as [ThisDocumentDateTime!3!Da
FROM #HEADER as Header
UNION
SELECT 2 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
ThisDocumentIdentifier.Doc
NULL
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
ON Header.DocumentID = ThisDocumentIdentifier.Doc
UNION
SELECT 3 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
ThisDocumentIdentifier.Doc
ThisDocumentDateTime.DocDa
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
ON Header.DocumentID = ThisDocumentIdentifier.Doc
JOIN #Header ThisDocumentDateTime
ON Header.DocumentID = ThisDocumentDateTime.Docum
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!
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-mic
- <Header>
- <ThisDocumentIdentifier>
<DocumentIdentifier>584D43
</ThisDocumentIdentifier>
- <ThisDocumentDateTime>
<DateTime>2006-10-11T08:20
</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!ThisDocumentIden
NULL as [Header!1!ThisDocumentDate
NULL as [Header!1!From!element],
NULL as [Header!1!To!element],
NULL as [ThisDocumentIdentifier!2!
NULL as [ThisDocumentDateTime!3!Da
FROM #HEADER as Header
UNION
SELECT 2 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
ThisDocumentIdentifier.Doc
NULL
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
ON Header.DocumentID = ThisDocumentIdentifier.Doc
UNION
SELECT 3 as tag,
1 as parent,
NULL,
NULL,
NULL,
NULL,
ThisDocumentIdentifier.Doc
ThisDocumentDateTime.DocDa
FROM #HEADER Header JOIN #Header ThisDocumentIdentifier
ON Header.DocumentID = ThisDocumentIdentifier.Doc
JOIN #Header ThisDocumentDateTime
ON Header.DocumentID = ThisDocumentDateTime.Docum
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!
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.
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.
Active 1 day ago
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
</ThisDocumentDateTime>
This is how it's supposed to look:
<ThisDocumentDateTime>
<DateTime DateTimeQualifier="On">200
</ThisDocumentDateTime>
I've been able to get as far as:
<ThisDocumentDateTime>
<DateTime>DateTimeQualifie
</ThisDocumentDateTime>
Could you show me how I would need to change the sql code to make this line happen?
Thanks again.
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
</ThisDocumentDateTime>
This is how it's supposed to look:
<ThisDocumentDateTime>
<DateTime DateTimeQualifier="On">200
</ThisDocumentDateTime>
I've been able to get as far as:
<ThisDocumentDateTime>
<DateTime>DateTimeQualifie
</ThisDocumentDateTime>
Could you show me how I would need to change the sql code to make this line happen?
Thanks again.
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.
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
In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 15 hours left to enroll
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.