-
1. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
masilamani Jun 20, 2014 5:32 PM (in response to anilallewar)Anil,
Can you try with below one ?
CREATE VIRTUAL PROCEDURE
BEGIN
SELECT t.* FROM XMLTABLE(XMLNAMESPACES('.................../types/' AS tns0), '/tns0:getEmpDataByNameResponseElement' PASSING WsView.getEmpDataByName_response.xml_in COLUMNS result string PATH '/tns0:result[1]') AS t;
END
Thanks, Mani
-
2. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 20, 2014 6:00 PM (in response to masilamani)Thanks Mani!!
I already got that working by using array notation in XPath; but the problem is it still doesn't allow me to get all the attributes. I only get the first attribute.
I have some ideas around using the array notation to solve my problem; basically create multiple columns with different positions in the result[]. The only caveat is that my response now can't vary based on the user input.
Anil
-
3. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
shawkins Jun 23, 2014 9:37 AM (in response to anilallewar)Array results are supported in Teiid 8.8 with [TEIID-2980] Expose sequences as arrays in XMLTABLE construct - JBoss Issue Tracker
Depending upon your needs you can also your context to be result:
... '/tns0:getEmpDataByNameResponseElement/tns0:result' PASSING WsView.getEmpDataByName_response.xml_in COLUMNS result string PATH 'text()') AS t;
-
4. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 25, 2014 5:56 PM (in response to shawkins)We use Teiid 8.4 and upgrading is not an option as of now. Additionally, I want to convert the 1 dimensional array of result to a 2 dimensional array so that I can group them into rows accordingly.
To this effect, I am thinking of writing a virtual procedure that uses XSL to transform the 1D XML to 2D and then expose the results via XMLTABLE. I'll probably have more questions around the usage later.
I did not like the option of selecting the whole XML in the resultset because the client would then know how to parse it; the details are not abstracted out from him.
-
5. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
shawkins Jun 26, 2014 10:06 AM (in response to anilallewar)> I want to convert the 1 dimensional array of result to a 2 dimensional array so that I can group them into rows accordingly.
To understand this better do you mean that each result is a row? If that's the case, then setting the context as the result element is what you want.
-
6. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 26, 2014 3:06 PM (in response to shawkins)No, a combination of some results would be a row based on the number of attributes that we passed to the web service to return.
So suppose that I asked information for 4 attributes and the criteria matches 3 records then a total of 12 records are returned. However since all the elements have the name <result>, I need to apply transformation to group these results so that we can have 3 rows of 4 columns each.
-
7. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
shawkins Jun 26, 2014 3:57 PM (in response to anilallewar)So you will need array support as xmltable can only process based upon a fixed number of columns. The only in between workaround is if there is some general amount of columns that you would want to map to such that you would get null values when actually retrieving fewer.
-
8. Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 27, 2014 12:06 PM (in response to shawkins)I did the mapping of the column; and that leads us to the next problem.
As per the example above for 4 attributes and the criteria matches 3 records then a total of 12 records are returned. I have mapped the 4 columns to the first four elements; but there is no way for me to get the next 4 elements as the next row in the XMLTABLE and so on. Hence my result now has 1 row always.
-
9. Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
shawkins Jun 27, 2014 2:16 PM (in response to anilallewar)It seems like an odd way to return the result. If there are just 12 sibling results to represent a 3x4 result, then as you were saying before, you'll have to use xslt or use more complicated xquery:
For example with a document like: <a><b>1</b><b>2</b><b>3</b><b>4</b></a> representing a 2 column result you could do something like:
{code}select * from xmltable('let $count := count($d/a/b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/a/b, (($i - 1)*$rows)+1, 2)}</row>' passing doc as d columns col1 string path 'b[1]', col2 string path 'b[2]') as x{code}
you would get a result of:
1,2
3,4
-
10. Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 29, 2014 9:07 PM (in response to shawkins)Steve,
Thanks for the pointer. I am now running into problems when using namespaces.
select * from xmltable('let $count := count($d/a/b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/a/b, (($i - 1)*$rows)+1, 2)}</row>' passing xmlparse(document '<a><b>1</b><b>2</b><b>3</b><b>4</b></a>') as d columns col1 string path 'b[1]', col2 string path 'b[2]') as x
Returns
col1 col2 1 2 3 4 However, if I use namespaces
select * from xmltable(XMLNAMESPACES('http://an.com/types/' AS ns0), 'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH 'b[1]') x
Returns
id <null> <null> Changing the path to '.' gives concatenated results.
select * from xmltable(XMLNAMESPACES('http://a.com/types/' AS ns0), 'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH '.') x
Returns
id 12
34 So now I am not able to convert the result to columns as the subsequence doesn't provide them as separate columns. Any ideas?
-
11. Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
shawkins Jun 30, 2014 8:44 AM (in response to anilallewar)In your first use of namespaces above you have the XMLNAMESPACES defined with an.com vs the document with a.com. You also need to use the namespace prefix in the path - 'ns0:b[1]'.
A small side note, you can also choose to ignore namespaces by using the * prefix.
-
12. Re: Re: Re: Re: Teiid 8.4 - Using XMLTABLE to get multiple values for SOAP WS call
anilallewar Jun 30, 2014 6:10 PM (in response to shawkins)The prefix of namespace in the XPath was the problem. The below query works fine.
select * from xmltable(XMLNAMESPACES('http://a.com/types/' AS ns0), 'let $count := count($d/ns0:a/ns0:b) let $rows := $count idiv 2 for $i in (1 to $rows) return <row>{fn:subsequence($d/ns0:a/ns0:b, (($i - 1)*$rows)+1, 2)}</row>' PASSING xmlparse(document '<ns0:a xmlns:ns0="http://a.com/types/"><ns0:b>1</ns0:b><ns0:b>2</ns0:b><ns0:b>3</ns0:b><ns0:b>4</ns0:b></ns0:a>') AS d COLUMNS id string PATH 'ns0:b[1]', id1 string path 'ns0:b[2]') x
Thanks a lot Steve!!