Why is work item Reportable REST API returning a different String Extensions field order? How to control String Extensions field order or return a unique XML TAG name?
I have encountered a critical issue with our Production implementation of RTC. I'm getting a different order of XML results between our RTC test and RTC production areas.
URL:
https://server:port/ccm/rpt/repository/workitem?fields=workitem/workItem[projectArea/name='MiCSES Change and Configuration Management' and (id=293)]/(children/stringExtensions[key='micsesSmStrCiName' or key='micsesSmStrCiVersion']/value)
Output from Test RTC (CI Name is listed before CI Version):
Output from Production RTC (CI Version is listed before CI Name):
Background:
I created and defined these 2 custom fields in Test RTC and Prod RTC in the same order.
We are using the XML to load data into a database table.
Questions:
1) Why is the returned XML order different between Test RTC and Prod RTC?
2) Is there a way to control the returned XML order of these fields? How?
3) Alternatively, is there a way to request/specify unique XML tags returned for each of the fields?
- In order to load the correct data into the correct column, we need a unique field ID. Right now all String Extensions are tagged with "<stringExtensions><value>" even when the values are coming from different custom fields.
2 answers
https://server:port/ccm/rpt/repository/workitem?fields=workitem/workItem[projectArea/name='MiCSES Change and Configuration Management' and (id=293)]/(children/stringExtensions[key='micsesSmStrCiName' or key='micsesSmStrCiVersion']/(key|value))
Comments
Thank you for the suggestion. I will try this.
Also, is it possible to somehow request the stringExtension more than once in the query? In other words, could I should request the first field separately and then request the second field separately? Somthing like the following? (The following doesn't work in my attempts. Is there a way to do this?)
https://server:port/ccm/rpt/repository/workitem?fields=workitem/workItem[projectArea/name='MiCSES Change and Configuration Management' and (id=293)]/(children/stringExtensions[key='micsesSmStrCiName']/value | stringExtensions[key='micsesSmStrCiVersion']/value)
Even if you were able to get a query similar to this working, I still don't think you could depend on the xml being generated in that same sequence. You will need to retrieve the keys and values and parse the xml or split the query and send two separate requests to the server.
Thanks for the follow-up. In my experience the last few months, it is possible to control the order the fields are output in the XML at least for the RTC provided fields because you can call them out by name and determine the order they are listed in the xpath syntax. For the RTC provided fields, the xPath syntax rder is the order the XML is output. Are your comments about varrying order meant to apply to the custom (extensions) fields?
I had thought about sending two separate requests also. What about the idea of switching one of the fields to a different type so that one is a string and one is something else? It seems I can control the order that way because I can list String Extensions vs BigDecimal for example in the order I want in the xPath..
Are there other data types besides Small String you might recommend for a 30 character name field or a 5 position version number field? I thought about Decimal for the version number but the issue with that is 1.10 is the same as 1.1 when using a decimal type.
Why is it important to you that the xml be generated in a particular sequence? I would think that as long as there is a means to identify the particular field you are after (like with the key) you should be able to write code to manipulate the xml however you need. I don't believe the REST API provides any guarantee on output sequence except for what is defined in the xml schema. Absent such a guarantee, behavior could change based on any number of factors (as you have seen in different environments).
Thanks. I think I'm beginning to understand a little better that the order of the fields output depends to a large degree on the xml schema definition. I can to some degree control the order of the xml nodes based on the order each field/xml node is requested with the xPath URL. But once an xml node is being displayed, it's going to list the fields in that node in the order the xml schema is defined for that node.
What I'm ultimately trying to accomplish is passing the xml to an Oracle SQL statement with xml paths defined to load the xml into a table with the appropriate columns. The problem at the moment is with two string extension fields, the RTC xml path is the same for each field. Oracle can't the difference. If the key value was somehow part of the xml path that lead to the value of the field, then I would have a unique path I could pass to Oracle.
Otherwise, I guess we'd have to write a program or script to do a double pass of the xml to determine line by line which field we're dealing with and to manually create a unique XML path.
What do you think about the following? I think because I am asking for two different xml structures it is allowing me to match on each key separately. I think this will always output the order of allExtensions first followed by stringExtensions.
But even more importantly than the order, if I have a unique XML path per field per row, the Oracle load will be able to distinguish between the fields. I think this will give me a unique xml path for the CI Name and CI version fields.
Am I missing something?
The only long term drawback I can think of is if we add any more string fields we'll run out of options for unique paths as I don't think there are many more ways to select the string fields.
https://server:port/ccm/rpt/repository/workitem?fields=workitem/workItem[projectArea/name='MiCSES Change and Configuration Management' and (id=293)]/(children/(allExtensions[key='MicsesSmStrCiName']/(smallStringValue|key)|stringExtensions[key='MicsesSmStrCiVersion']/(value|key)))
So you need unique xPath expressions to properly load the results of your query into Oracle. I'm not sure how Oracle consumes the xml you pass it, but assuming you modify your query to return both the key and value, would xPath like this work:
//workitem/children/stringExtensions[contains(./key,'MicsesSmStrCiName')]/value
//workitem/children/stringExtensions[contains(./key,'MicsesSmStrCiVersion')]/value
I think the other solution would be for you to write a transformation of the xml returned by RTC into a more deterministic format.
Thanks for the suggestion. I'm not familiar with the "contains" syntax.
Is there a place where these is more documentation on that type of command?
Is there a way for me to incorporate your suggestion into one xPath command or does this have to be run as two separate commands?
Oracle XML (comment 1 of many):
Let me try to give a better idea of how Oracle consumes the XML. It all comes down to the XML output. The xPath syntax is just the tool we're using to generate XML in a format that Oracle can consume.
Oracle knows how to read XML based on the begin and end XML tags. It allows you to define paths that are mapped to specific database fields/columns. I'll use the following examples:
1a) <stringExtensions><key>MicsesSmStrCiVersion</key><value>1.5</value></stringExtensions>
1b) <stringExtensions><key>MicsesSmStrCiName</key><value>SPKG_GUIDELIN.PKB</value></stringExtensions>
The path /stringExtensions/value/ would yield two different results/fields: 1.5 and SPKG_GUIDELIN.PKB. But we want the XML path to be unique per field. That would require some kind of unique XML path for each. (See next comment.)
Oracle XML (continued - comment 2):
The following would all unique Oracle XML paths per field:
2a) <stringExtensions><MicsesSmStrCiVersion><value>1.5</value></MicsesSmStrCiVersion></stringExtensions>
2b) <stringExtensions><MicsesSmStrCiName><value>SPKG_GUIDELIN.PKB</value></MicsesSmStrCiName></stringExtensions>
3a) <stringExtensions><MicsesSmStrCiVersion><value>1.7</value></MicsesSmStrCiVersion></stringExtensions>
3b) <stringExtensions><MicsesSmStrCiName><value>CI_3</value></MicsesSmStrCiName></stringExtensions>
For row #2:
The path /stringExtensions/MicsesSmStrCiName/value/ would yeild SPKG_GUIDELIN.PKB (the CI Name field).
The path /stringExtensions/MicsesSmStrCiVersion/value/ would yeild 1.5 (the CI Version field).
For row #3, the same paths would yield CI_3 and 1.7 for the CI Name and CI Version fields respectively.
Oracle XML (continued - comment 3 / final):
But I think XML schema would have to change to provide this kind of output. If I'm understanding how the RTC XML schema works, I think the value would have to be an attribute or sub-element of the key somehow.
I'm using the key value in this example because I know it will always be unique between these two fields. If there is something else that will always be unique for each field and that will repeat from row to row, I could use that instead.
This is why using allExtensions for one field and stringExtensions for the other field may work because it results in a unique XML path for each field that repeats for each row.
Example:
https://server:port/ccm/rpt/repository/workitem?fields=workitem/workItem[projectArea/name='MiCSES Change and Configuration Management' and (id=293)]/(children/(allExtensions[key='MicsesSmStrCiName']/(smallStringValue|key)|stringExtensions[key='MicsesSmStrCiVersion']/(value|key)))
I also cannot get the following syntax to work as was suggested:
[contains(./key,'MicsesSmStrCiName')]
Is there a place where these is more documentation on that type of command?
Is there a way for me to incorporate your suggestion into one xPath command or does this have to be run as two separate commands? You had previously suggested:
//workitem/children/stringExtensions[contains(./key,'MicsesSmStrCiName')]/value
//workitem/children/stringExtensions[contains(./key,'MicsesSmStrCiVersion')]/value
Regarding previous comments "Oracle XML (continued - comment 2): " and comment 3, another way to accomplish the unique XML path would be if the value tag were to list the key value as part of the value tag.
For example:
This: <MicsesSmStrCiVersionValue>1.7</MicsesSmStrCiVersionValue>
instead of
This: <value>1.7</value>
Comments
Michael Taylor
Sep 17 '13, 12:10 p.m.FYI - this is related to a question from about 3 weeks ago:
https://jazz.net/forum/questions/124948/how-to-use-work-item-reportable-rest-api-to-select-subset-of-string-extentions
Michael Taylor
Sep 17 '13, 1:02 p.m.Is someone knowledgeable about the RTC work item Reportable REST API able to look at this? This issue is holding up our Production implementation. Thanks in advance.