CODEWITHSUNDEEP
xmloracle-databasebiztalk
Andy
Andy

Reputation: 2318

Can you use the oracle dataadapter to select an XML from a database

Using an xmlagg query (select xmlagg( xmlelement (... ) to select an XML from database would allow more control over the format of the data we pickup.

generating the schema based of the query gives us the standard polingstatments with a single record (which contains the xml). creating the schema for that xml is easy as well (following the advice on example)

However, adding this schema to the datastructure type of the element doesn't work. i can select the schema in the dropdown but it resets back to the previous selected entry when selected but any other entry works. (in the below image, the order_header is the one i'd like to use.)

DataStructure type

i tried ignoring schema's and seeing if i could just map the results but the data in the pollingstatement is actually escaped

<POLLINGSTMTRECORD><XML>&lt;order_header&gt;&lt;order_id&gt;206817&lt;/order_id&gt;

Has anyone ever combined the oracle dataadapter with an xml query? Any clue where i went wrong? would a stored procedure be a better solution to this? could i find a way the data doesn't change the < to &lt;

Upvotes: 1

Views: 206

Answers (2)

Andy
Andy

Reputation: 2318

The solution was rather simple but a bit hard to find documentation on.

I created the receiveport by consuming an adapter service and putting the XML query in the bindings. this created a Schema up to the node that contained the XML (in my case, this was called XML).

auto generated item

if you would just run this, it would create an XML where you created XML was a String in the XML element (all <> where escaped as <>).

To get the element out of the polling-statement you first need to create a Schema of how your result will look like (This can be easily done by generating a schema from a well-formed XML)

Then change the receiveport "Inbound BizTalk Message body" so the source is "Path" and copy the instance xpath from Visual Studio for the XML element (properties on the element holding the XML data)

here be instance XPaths

Copy the xpath from the element properties to the receiveport settings. You could write the Xpath but i can't recomend that. (it's a bit peculiar on the namespacing)

receive port settings

At this point the message that comes in is an XML that follows your result XML schema and can be used as any XML in BizTalk (and actually a bit easier then the standard one you get when working with the Oracle adapter, since you got rid of the pollingstmt/pollingrecord/pollingrecord structure)

I'd like to mention that your root node needs to be unique and the xml gotten by the xpath needs to find a Schema for it.

Upvotes: 2

Dan Field
Dan Field

Reputation: 21641

You have a few options here:

Typed Polling

If you use TypedPolling, you'll have to extract the message (which will be embedded as CDATA in an xs:string element) in one of two ways:

XmlPolling

If you use XmlPolling, you could do a neat little trick:

  1. Create a 'dummy' envelope schema (a single record with an xs:any child, envelope = true, BodyXPath = the <Any> node)
  2. Set the xmlStoredProcedureRootNodeName to your root node of the envelope schema
  3. Set the xmlStoredProcedureRootNodeNamespace to the namespace of your envelope schema
  4. Create a schema mathing the XML your Oracle query produces

Now the XML Recieve pipeline will "debatch" your query to the schema you produce. This debatching is necessary because a query could, in theory, produce multiple documents in a single result (which would have multiple root nodes, which would be invalid). You could, alternatively, design your query to produce everything but the root node and then just use the adapter settings to assign the root node, but I don't like that method as much myself (I prefer being able to quickly and easily test results of the query without modification).

Upvotes: 1

Related Questions