Storing data from given XML into Database

Markus Eßmayr

unread,

Nov 29, 2006, 7:10:19 AM11/29/06

to

Hello BizTalk-experts!

Environment:
Microsoft Windows Server 2003 SP1
Microsoft BizTalk Server 2004 ( yes 2004, we'll get 2006 in a few weeks, but
I have to solve this thing now :( )

It seemed to me beeing quite simple to do "Store the data from an XML-file
into a database".
But during work I ran into the following questions:

1. The given XML and it's schema is quite big. So I thought "There must be
some tool, that is able to generate a database schema out of the
XML-schema!"
But I couldn't find anything that worked. Even XMLSpy 2007 produced a very
unsatisfying result.
Does anybody of you know a tool for that task?

2. So I created a DB-schema for the first parts of data and also created the
BizTalk-application.
The XML-schema defines lots of complex types and tree structures, and there
are also much fields that are optional (minoccurs=0).
So I created a map in BizTalk that should transform the input message into a
message for the stored procedure to call for the data-INSERT. This map also
included some assignments of fields, that are defined in the schema but do
not exist in my input message instance. To be on the safe side, I also added
"Logical Existence" and assigned "Value Mapping" functoids to the assignment
in the map.
But when executing the map, I got the following message in the event-log:

Uncaught exception terminated service
RACONLinz.BizTalk.KAGFondsmeldeschnittstelle.KAGFondsmeldeschnittstelleDownloadOrchestration(2b21a791-6e4f-0697-cfd1-eccef2f37f9f),
instance 51d99978-8b0a-44eb-b738-fdbfeda47a67
An error occurred while processing the message, refer to the details section
for more information
Exception type: XlangSoapException
Source: Microsoft.XLANGs.BizTalk.Engine
Target Site: Void VerifyTransport(Microsoft.XLANGs.Core.Envelope, Int32,
Microsoft.XLANGs.Core.Context)
Help Link:
Additional error information:

HAT says:

Microsoft.XLANGs.Core.XlangSoapException: An error occurred while processing
the message, refer to the details section for more information
at
Microsoft.BizTalk.XLANGs.BTXEngine.BTXPortBase.VerifyTransport(Envelope env,
Int32 operationId, Context ctx)
at Microsoft.XLANGs.Core.Subscription.Receive(Segment s, Context ctx,
Envelope& env, Boolean topOnly)
at Microsoft.XLANGs.Core.PortBase.GetMessageId(Subscription subscription,
Segment currentSegment, Context cxt, Envelope& env, CachedObject location)
at
RACONLinz.BizTalk.KAGFondsmeldeschnittstelle.KAGFondsmeldeschnittstelleDownloadOrchestration.segment1(StopConditions
stopOn)
at Microsoft.XLANGs.Core.SegmentScheduler.RunASegment(Segment s,
StopConditions stopCond, Exception& exp)
Microsoft.XLANGs.Core.XlangSoapException

Why ...SoapException? I definitely don't call any webservice!
Has anybody of you an idea, what I could try now?
If you need more information to help me, please just ask for it!

THANK YOU VERY, VERY MUCH!
Max

GR

unread,

Nov 29, 2006, 8:53:55 AM11/29/06

to

Max,
I dont have an answer for you but have a look at the following blog by
Matt Melenksi:
http://objectsharp.com/blogs/matt/archive/2005/08/31/3209.aspx

There's a downloadable project there
(http://www.objectsharp.com/blogs/matt/images/BtsCompareSplitterPatterns.zip)
that is relatively easy to get up and running. The project receives a
big file, dumps it into a sql database and then loops through the
database.

The project itself is a very useful reference.

Garth

Markus Eßmayr

unread,

Nov 30, 2006, 2:18:18 AM11/30/06

to

Hi Garth,

thanks for the links.
I took a short look on that, but it seems, that this sample is used to
import a big XML file with a few fields but a big bunch of records into a
database.
My XML instead has just one "record" in it, but this record consists of a
lot of fields.
But even worse, the fields are not in a flat structure. Subtables and
structures are used. :(
I hope I'll find somehing!

Thanks again!
Max

"GR" <ga...@silverminute.com> schrieb im Newsbeitrag
news:1164808435.6...@16g2000cwy.googlegroups.com...

Matt Meleski

unread,

Nov 30, 2006, 8:51:00 AM11/30/06

to

The example that was discussed:
http://objectsharp.com/blogs/matt/archive/2005/08/31/3209.aspx
can be expanded to deal with a much more complicated structure. You can use
openxml in the stored procedure to also shred out data from an xml message
that has a more complex hierarchical structure. XPath like syntax can be used
in the stored procedure to navigate to the relevant nodes. If you are using
Sql 2005, there are more options to process the xml, such as discussed here:
http://objectsharp.com/blogs/matt/archive/2006/07/19/5520.aspx

For other options:

Have you considered using updategrams, or as you say , if you have only one
record, creating a stored procedure that accepts a number of parameters that
represents the record and then calling the stored procedure from BizTalk?

Matt

Markus Eßmayr

unread,

Dec 4, 2006, 8:43:28 AM12/4/06

to

Hello!

This is some kind of "sub-question" that will help me solving my problem
mentioned as the second in my last posting.

What do I have to do, that the BizTalk-map sends NULL to the destination
message, if the element doesn't exist in the source message? If the element
does exist, it should simply copy the value.

I cannot get this to work!

Thanks in advance!
Max

"Markus Eßmayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:%23ywXY96...@TK2MSFTNGP06.phx.gbl...

GR

unread,

Dec 4, 2006, 1:27:11 PM12/4/06

to

I tried to mail you a simple solution to resolve the null field problem
you were having. If you did not get it please add something to this
post I'll try again.

As for the original problem. My suggestion would be to
1) go to Matt Melenksi's blog
(http://objectsharp.com/blogs/matt/archive/2005/08/31/3209.aspx) and
get the demo solution that he has posted. (btw thanks for that posting
Matt)
2) Get that running
3) Strip out as much as possible from Matt Melenski's solution until
you have the bare bones of that solution putting something very simple
into a sql table. (this will help you get some warm and fuzzies)
4) Then look at what you have left of Matt's solution and use it as a
reference and get your solution to put something simple into a sql
table.
5) Keep incrementing the complexity of your solution (ie once it's
working for putting something simple into a table then try putting a
value into another table). Instead of hitting the full complex solution
all at once.

Markus Eßmayr

unread,

Dec 5, 2006, 7:48:20 AM12/5/06

to

Thanks for your example!
As I wrote to you in my e-mail.
At the moment I'm stuck on BTS2004 which hasn't got the "Nil Value" functoid
yet.

Solution for BTS2004?

Max

"GR" <ga...@silverminute.com> schrieb im Newsbeitrag

news:1165256831.1...@j44g2000cwa.googlegroups.com...

cdu...@ideacorporation.com

unread,

Dec 5, 2006, 9:28:51 AM12/5/06

to

It's pretty straightforward. You need to use a custom xslt script and
attach it to the element in question.

In the script test for the existence of an element or attribute. If it
does not exist, add the nill attribute. If it does exist, add the
value:

<xsl:choose>

<xsl:when
test="./*[local-name()='fis_oper_resourceassignments']/*[local-name()='ResourceType'
and text()='Stand']/../*[local-name()='ResourceID']" >
</xsl:when>

<xsl:otherwise>

<xsl:attribute name="xsi:nil"
namespace="http://www.w3.org/2001/XMLSchema-instance">true</xsl:attribute>

</xsl:otherwise>

</xsl:choose>

</xsl:element>

Markus Eßmayr

unread,

Dec 7, 2006, 3:45:16 AM12/7/06

to

Hello again!

Thank you all for your hints and comments.
After playing around with SQLServer 2005 for a while, I now decided to
insert the complete XML-message into the database (into a table with an
XML-column).
As it's not defined, which data the customer wants to get out of that XML,
that makes me more flexible.
I'll just create specific views, so that the customer has an easy access to
the data inside the XML.
Ok, the access won't be the fastest, but thats no problem in this case.

Too bad, that I ran into another problem with BTS, but I think, that this is
just because I'm no expert yet.

That should happen:
1. I read the XML-file (with given schema) into BTS.
2. I map the Key-Values to the target-schema for the database-INSERT.
3. Set the complete source-XML-message as string-input into the
db-INSERT-schema.
4. Store the whole thing.

My problem is step 3. I couldn't get BTS to set the whole source-message
(including XML-tags) into a string in the target schema.

Could anyone please help me with this!

Thanks very much in advance!
Max

"Markus Eßmayr" <essmayr/at/racon-linz.at> schrieb im Newsbeitrag
news:%23ywXY96...@TK2MSFTNGP06.phx.gbl...

cdu...@ideacorporation.com

unread,

Dec 7, 2006, 8:55:54 AM12/7/06

to

I think there are two possible things to try, but you will probably
have to play with both ideas, as I don't have Biztalk handy right now:

1. Make the type of the element you want to map the whole document to
in db-INSERT-schema xs:Any instead of string. Use the Mass Copy
functiod, and make sure to set the link (right click the link,
propertied) to Copy Text and subcontent.

2. Make the type of the element you want to map the whole document to
in db-INSERT-schema xs:Any instead of string. Make the element a
distinguished property in the schema. Use an assignment shape to set
the distinguished property to the original message.

Chuck

luogo interessante, soddisfare interessante, buon! http://www.bikini.741.com

unread,

unread,

Mar 31, 2007, 1:16:17 PM3/31/07

to

disegno piacevole, lavoro grande :) http://www.qukeartiere90.org/erotik