Use XML document as POST body in httpInvoke

[bpiepers]

I have the requirement of calling a webservice for which I should be able to use the ws translator and the invokeHttp method. I have to pass an xml document in the body of the POST request, however. According to the Development Guide Reference (paragraph 13.18.5) I should be able to use the request parameter for this. Can somebody give me an idea as to how to pass in an xml document as the request of the call? I would expect something like this:

EXEC invokehttp_proxy_src.invokeHttp('POST', request, 'endpoint', 'TRUE')) AS f, XMLTABLE('root' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS columndefinitions

For clarity: I would like to pass in an xml body as the request body in the above call. Is that possible?

[rareddy]

Bas,

Yes. In your example "request" can be XML. If you need to build XML request based on scalar values from your procedure input, then you can use XMLFunctions to do that. See XML Functions - Teiid 8.8 (draft) - Project Documentation Editor, You can even build a dynamic request document based on select from another query.

Ramesh..

[bpiepers]

Thanks Ramesh. I would be very interested in an example as to how to accomplish this. Especially building a dynamic request document based on select from another query. Any documentation on this? Would you be able to provide an example?

Bas

[shawkins]

Building the request document is just a matter of creating the xml that you want.  Teiid Designer has support for an XMLdocument model that provides the ability to map to an XSD.  Otherwise you can perform manual construction using the SQL/XML functions form the link that Ramesh has provided above.

For example the usage of xmlagg, xmlforest, and xmlelement can quickly create a document.  With a users table that contains firstName and lastName columns:

{code}
SELECT xmlelement(name 'users', xmlagg(xmlelement(name 'user', xmlforest(firstName, lastName)))) from users
{code}

would produce the document:

{code}
<users>
  <user>
    <firstName>Some</firstName>
    <lastName>One</lastName>
  <user>
  <user>
    <firstName>Another</firstName>
    <lastName>Person</lastName>
  <user>
...
{code}

[bpiepers]

Thank you, Steven. I was already trying to construct an xml document using the functions from the documentation. Although the capabilities and presence of these kinds of functions can be looked up relatively easy using the documentation, it doesn't seem to be documented as to how to use these functions in the transformation editor of Teiid. I will try to use your example in my use case and let you know if it worked.

[bpiepers]

I thought it would be worthwhile how I finally managed to dynamically compose an XML document in Teiid using that to invoke the Rest interface:

SELECT

        A.Duration AS duration

    FROM

        (EXEC sourcemodel.invokeHttp('POST', (SELECT XMLELEMENT(NAME Request, XMLELEMENT(NAME ResourcesQuery, XMLATTRIBUTES('event' AS resourceType), XMLELEMENT(NAME ResourceIds, XMLAGG(XMLELEMENT(NAME ResourceId, rec.contentItemId))), XMLELEMENT(NAME "Options", XMLELEMENT(NAME "Option", XMLATTRIBUTES('Props' AS type), 'channels,Duration,AvailabilityStart,AvailabilityEnd,Products')), XMLELEMENT(NAME SubQueries, XMLELEMENT(NAME SubQuery, XMLATTRIBUTES('Titles' AS relationName), XMLELEMENT(NAME "Options", XMLELEMENT(NAME "Option", XMLATTRIBUTES('props' AS type), 'Name,Genres,ShortSynopsis,LongSynopsis,MinimumAge,SeriesCollection,ProductionDate,ProductionLocations,Actors,Directors,Pictures,IsAdult'))), XMLELEMENT(NAME SubQuery, XMLATTRIBUTES('Products' AS relationName), XMLELEMENT(NAME "Options", XMLELEMENT(NAME "Option", XMLATTRIBUTES('props' AS type), 'EntitlementState,Type,Currency,IsAvailable,ListPrice')))))) FROM reng_pref_recommendations_edl.reng_pref_recommendations AS rec), 'http://endpointurl', 'TRUE')) AS f, XMLTABLE(XMLNAMESPACES(DEFAULT 'urn:eventis:traxisweb:1.0'), '/events/Event' PASSING XMLPARSE(DOCUMENT f.result) COLUMNS Duration string PATH '/Duration/text()') AS A

Note that the endpoint has been replaced in the above example by a nonsense url. The dynamic part is bold. It selects one column from a view with an alias of "rec" and puts that in the xml document. This produces an xml document that looks as follows (dynamic part in bold with example values):

<Request>

        <ResourcesQuery

            resourceType="event">

            <ResourceIds>

               <ResourceId>

                    1

                    </ResourceId>

                <ResourceId>

                   2

                    </ResourceId>

                <ResourceId>

                   3

                    </ResourceId>

              etc

                <ResourceId>                

                </ResourceIds>

            <Options>

                <Option

                    type="Props">

                    channels,Duration,AvailabilityStart,AvailabilityEnd,Products

                    </Option>

                </Options>

            <SubQueries>

                <SubQuery

                    relationName="Titles">

                    <Options>

                        <Option

                            type="props">

                            Name,Genres,ShortSynopsis,LongSynopsis,MinimumAge,Se                                                                                                                                                             riesCollection,ProductionDate,ProductionLocations,Actors,Directors,Pictures,IsAd                                                                                                                                                             ult

                            </Option>

                        </Options>

                    </SubQuery>

                <SubQuery

                    relationName="Products">

                    <Options>

                        <Option

                            type="props">

                            EntitlementState,Type,Currency,IsAvailable,ListPrice

                            </Option>

                        </Options>

                    </SubQuery>

                </SubQueries>

            </ResourcesQuery>

        </Request>

Note that there should also be a way to do this more elegently by for example defining a view that is used in the post request of the invokeHttp call rather than generating this rather hard coded with all these XMLELEMENT calls. For smaller XML documents this shouldn't be a problem but for larger XML document this quickly becomes too complex.

[shawkins]

> For smaller XML documents this shouldn't be a problem but for larger XML document this quickly becomes too complex.

Yes, what you have looks good.  A couple of things that may help if you have large static pieces of the document.  You could use just XMLPARSE with the literal form of Subqueries and Options if they always stays the same rather than using XMLELEMENT.   There is also the XMLQUERY function that will allow you to use XQUERY to build your desired document, for example:

XMLQUERY('<Request>...{$dyn}...</Request>' passing (SELECT XMLELEMENT(NAME ResourceIds, XML(XMLELEMENT(NAME ResourceId, rec.contentItemId))) ... as dyn)

[bpiepers]

Thank you, that's very useful!