XPATH and missing values

First of all, sorry for my english

Hi all, i have one problem when extract data with xpath from xml data.

I explain:

This is the real data in format table

CAR ID Year in In use Driver Year Out
1540 2014 YES JOHN  
1033 2010 NO    
1622 2016 YES PETER  
1715 2016 NO   2016
1333 2014 NO    
1218 2012 NO   2013

Or... The XML data

<CARS>
	<Car registry="1118">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1540</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>YES</data>
		</columnID>
		<columnID value="3">
			<name>DRIVER</name>
			<data>JOHN</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2014</data>
		</columnID>
	</Car>
	<Car registry="1133">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1033</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>NO</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2010</data>
		</columnID>
	</Car>
	<Car registry="1144">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1622</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>YES</data>
		</columnID>
		<columnID value="3">
			<name>DRIVER</name>
			<data>PETER</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2016</data>
		</columnID>
	</Car>
	<Car registry="1155">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1715</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>NO</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2014</data>
		</columnID>
		<columnID value="5">
			<name>Year out</name>
			<data>2016</data>
		</columnID>
	</Car>
	<Car registry="1166">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1333</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>NO</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2014</data>
		</columnID>
	</Car>
	<Car registry="1177">
		<columnID value="1">
			<name>CAR ID</name>
			<data>1218</data>
		</columnID>
		<columnID value="2">
			<name>In use</name>
			<data>NO</data>
		</columnID>
		<columnID value="4">
			<name>Year in</name>
			<data>2012</data>
		</columnID>
		<columnID value="5">
			<name>Year out</name>
			<data>2013</data>
		</columnID>
	</Car>
</CARS>

I use the XPATH node for extract the five columns

with this information

Column name XPath query Type
CAR ID /CARS/Car/columnID[@value="1"]/data Integer (multiple rows)
Year in /CARS/Car/columnID[@value="4"]/data string (multiple rows)
In use /CARS/Car/columnID[@value="2"]/data string (multiple rows)
DRIVER /CARS/Car/columnID[@value="3"]/data string (multiple rows)
Year Out /CARS/Car/columnID[@value="5"]/data string (multiple rows)

... And mark "return missing" if not in each value....

 

The resut table is....

Row ID CAR ID Year in In use Driver Year Out
Row0_1 1540 2014 YES JOHN 2016
Row0_2 1033 2010 NO PETER 2013
Row0_3 1622 2016 YES ? ?
Row0_4 1715 2016 NO ? ?
Row0_5 1333 2014 NO ? ?
Row0_5 1218 2012 NO ? ?

But that... IS WRONG!!!

Xpath extract the each column wiith the non empty cells first, and after put missing cells, not respecting each record.

What I can do????

The nodes used for this test are:

Node Information
Table creator Filled the first cell of the first column with the xml data
String to XML For arrange the string with a correct XML structure
XPath with the data in the table mentioned previously

 

Hi,

It is the correct behavior if you are using multiple queries. Each query results in a set of values, which will be written into the columns. They don't kow of each others, that why they are not written next to each other. 

you need to do this in two steps.

the first XPath nodes extracts only the cars as new xml cells.

The second xpath node than extract the values from each car.

let me know if you need a workflow. Btw. it is always very helpful if we have a starting workflow, which we can edit.

Cheers, Iris

2 Likes