InfoPath Dev

 My InfoPath 2010 browser form utilizes a data connection to an XML file.  The simple little file used to look like this:

<?xml version="1.0"?>
<query xsi:noNamespaceSchemaLocation="" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" queryname="UTA_HR_UTA20_ALL" numrows="747">
<row rownumber="1">
<F.BUSINESS_UNIT>
![CDATA[UTA20]]>
</F.BUSINESS_UNIT>
<F.DESCR>
<![CDATA[College of Nursing]]>
</F.DESCR>
<A.EMPLID>
<![CDATA[1000036503]]>
</A.EMPLID>
<E.FIRST_NAME>
<![CDATA[Mary]]>
</E.FIRST_NAME>

etc. 

Recent changes to our PeopleSoft/Oracle setup now render that same query like this:

<?xml version="1.0"?>
<webRowSet xmlns="http://java.sun.com/xml/ns/jdbc">
<properties>
<escape-processing>true</escape-processing>
<fetch-direction>1000</fetch-direction>
<fetch-size>0</fetch-size>
<isolation-level>1</isolation-level>
<key-columns/>
<map/>
<max-field-size>0</max-field-size>
<max-rows>0</max-rows>
<query-timeout>0</query-timeout>
<read-only>true</read-only>
<show-deleted>false</show-deleted>
<table-name/>
</properties>
<metadata>
<column-count> 19</column-count>
<column-definition>
<column-index>1</column-index>
<auto-increment>false</auto-increment>
<case-sensitive>false</case-sensitive>
<currency>false</currency>
<nullable>0</nullable>
<signed>false</signed>
<searchable>false</searchable>
<column-display-size>0</column-display-size>
<column-label>Unit</column-label>
<column-name>F.BUSINESS_UNIT</column-name>
<schema-name/>
<column-precision>0</column-precision>
<column-scale>0</column-scale>
<table-name/>
<catalog-name/>
<column-type>12</column-type>
<column-type-name>VARCHAR</column-type-name>
</column-definition>
and lots more column definitions, and finally data that looks like
<data>
<currentRow>
<columnValue>
<![CDATA[UTA20]]>
</columnValue>
<columnValue>
<![CDATA[College of Nursing]]>
</columnValue>
<columnValue>
<![CDATA[1000036503]]>
</columnValue>

Neither InfoPath Form Services 2010 or SharePoint 2010 data-view will read it the new format.  Is there a way to convert it back to the old/simpler format?  Thank you!

--Susan 

 So - you get an error when you try to connect to the XML file? What is the error? Or is the issue that the schema is hard to work with because of it coming back as a repeating structure and the data being all in tags w/ columnValue for the name?

Realized that I have to create new data connection since structure is different.  I was able to create data connection with no error.  Now have to figure out how to retrieve data from it and rework my rules.  Maybe it's like reading the data that comes from GetUserProfile web service - will give that a try. 

Previously, using the simple-format XML data source, I queried the entire data source, then set a field to

xdXDocument:GetDOM("SimpleXML")/query/row/C.POSITION_NBR[../A.EMPLID = xdXDocument:get-DOM()/my:myFields/my:requestorGrp/my:ReqEmplID and contains(../C.DESCR, "- AP")]

using complicated XML structure, how do I set field to

xdXDocument:GetDOM("UTA_HR_UTA20_ALL-BAD")/ns1:webRowSet/ns1:data/ns1:currentRow/ns1:columnValue[../../../ns1:metadata/ns1:column-definition/ns1:column-label = "C.POSITION_NBR"]
  from the same row where
xdXDocument:GetDOM("UTA_HR_UTA20_ALL-BAD")/ns1:webRowSet/ns1:data/ns1:currentRow/ns1:columnValue[../../../ns1:metadata/ns1:column-definition/ns1:column-label = "A.EMPLID"] = xdXDocument:get-DOM()/my:myFields/my:requestorGrp/my:ReqEmplID
  and
contains(xdXDocument:GetDOM("UTA_HR_UTA20_ALL-BAD")/ns1:webRowSet/ns1:data/ns1:currentRow/ns1:columnValue[../../../ns1:metadata/ns1:column-definition/ns1:column-label = "C.DESCR"], "- AP") 

MANY THANKS FOR ANY GUIDANCE YOU CAN OFFER!  --Susan

Oh wow. That data structure is ... ick. Can you attach a sample XML file (replace any personal data, of course) or email me one? Looking at the schema I don't see any way to association the repeating column value field back to the schema to figure out which instance you want.

 Worst XPath EVER. Here's from a test form I set up:

xdXDocument:GetDOM("UTA_HR_UTA20-BAD")/ns1:webRowSet/ns1:data/ns1:currentRow[ns1:columnValue[number(xdXDocument:GetDOM("UTA_HR_UTA20-BAD")/ns1:webRowSet/ns1:metadata/ns1:column-definition/ns1:column-index[../ns1:column-name = "A.EMPLID"])] = xdXDocument:get-DOM()/my:myFields/my:field1 and contains(ns1:columnValue[number(xdXDocument:GetDOM("UTA_HR_UTA20-BAD")/ns1:webRowSet/ns1:metadata/ns1:column-definition/ns1:column-index[../ns1:column-name = "C.DESCR"])], "- AP")]/ns1:columnValue[number(xdXDocument:GetDOM("UTA_HR_UTA20-BAD")/ns1:webRowSet/ns1:metadata/ns1:column-definition/ns1:column-index[../ns1:column-name = "C.POSITION_NBR"])] 

For you, the bold text needs to change to your XPath:  xdXDocument:get-DOM()/my:myFields/my:requestorGrp/my:ReqEmplID

I'll add that this is pretty slow to return a result in my simple test form. There may be a way to optimize it - however, when I tried to use relative paths to the column-index fields I wasn't getting any results, and figured this would at least give you a starting point. :)