cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

XPATH Expression to Count the Row nodes for XML ( Excel workbook format xml)

Go to solution
akshay108
Explorer

‎03-06-2024 12:37 PM - edited ‎03-06-2024 12:43 PM

0 Kudos

Hello Experts,

Need some inputs in creating XPath expression to count row nodes. I know we can use count() function, however its not giving desired results.

Input is XML payload mentioned below or here.

Xpath I'm using is -     count(/ss:Workbook/ss:Worksheet[@ss:Name='Credit Cards']/ss:Table/ss:Row)

akshay108_0-1709728048390.png

Namespace declared -  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

akshay108_1-1709728078385.png

When I tested the same XML and XPath expression on http://xpather.com/ it gave Expected results ( RowCount = 2)

akshay108_2-1709728152032.png

 

However, in CPI the result is RowCount = 0 

akshay108_3-1709728251918.png

Can anyone please guide what is missing here ?

----------------------------------------------------------------------------

Input XML :

<?xml version="1.0" encoding="UTF-8"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>AB</Author>
<LastAuthor/>
<Created>2006-03-17T14:40:25Z</Created>
<LastSaved>2019-01-03T16:09:17Z</LastSaved>
<Company>END USER</Company>
<Version>16.00</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<AllowPNG/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8190</WindowHeight>
<WindowWidth>20430</WindowWidth>
<WindowTopX>0</WindowTopX>
<WindowTopY>0</WindowTopY>
<TabRatio>493</TabRatio>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Arial" x:Family="Swiss"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="s62">
<Borders/>
<Font ss:FontName="Verdana" x:Family="Swiss"/>
<Interior/>
<NumberFormat ss:Format="@"/>
</Style>
<Style ss:ID="s63">
<Alignment ss:Horizontal="Center" ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Verdana" x:Family="Swiss"/>
<Interior/>
<NumberFormat ss:Format="@"/>
</Style>
</Styles>
<Worksheet ss:Name="Credit Cards">
<Table ss:ExpandedColumnCount="24" ss:ExpandedRowCount="2" x:FullColumns="1" x:FullRows="1" ss:StyleID="s62" ss:DefaultRowHeight="12.5625">
<Column ss:StyleID="s62" ss:Width="51"/>
<Column ss:StyleID="s62" ss:Width="81.75"/>
<Column ss:StyleID="s62" ss:Width="61.5"/>
<Column ss:StyleID="s62" ss:Width="116.25"/>
<Column ss:StyleID="s62" ss:Width="92.25"/>
<Column ss:StyleID="s62" ss:Width="84"/>
<Column ss:StyleID="s62" ss:Width="36.75"/>
<Column ss:StyleID="s62" ss:Width="24.75"/>
<Column ss:StyleID="s62" ss:Width="118.5"/>
<Column ss:StyleID="s62" ss:Width="84"/>
<Column ss:StyleID="s62" ss:Width="44.25"/>
<Column ss:StyleID="s62" ss:Width="63.75"/>
<Column ss:StyleID="s62" ss:Width="67.5"/>
<Column ss:StyleID="s62" ss:Width="78.75" ss:Span="1"/>
<Column ss:Index="16" ss:StyleID="s62" ss:AutoFitWidth="0" ss:Width="66.75"/>
<Column ss:StyleID="s62" ss:Width="57.75"/>
<Column ss:StyleID="s62" ss:Width="68.25" ss:Span="1"/>
<Column ss:Index="20" ss:StyleID="s62" ss:Width="60"/>
<Column ss:StyleID="s62" ss:Width="65.25" ss:Span="1"/>
<Column ss:Index="23" ss:StyleID="s62" ss:Width="42"/>
<Row ss:AutoFitHeight="0" ss:StyleID="s63">
<Cell>
<Data ss:Type="String">Login ID</Data>
</Cell>
<Cell>
<Data ss:Type="String">Display Name</Data>
</Cell>
<Cell>
<Data ss:Type="String">Card Type</Data>
</Cell>
<Cell>
<Data ss:Type="String">Credit Card Number</Data>
</Cell>
<Cell>
<Data ss:Type="String">Expiration Date</Data>
</Cell>
<Cell>
<Data ss:Type="String">Name on Card</Data>
</Cell>
<Cell>
<Data ss:Type="String">Street</Data>
</Cell>
<Cell>
<Data ss:Type="String">City</Data>
</Cell>
<Cell>
<Data ss:Type="String">State/Province/Region</Data>
</Cell>
<Cell>
<Data ss:Type="String">Zip/Postal Code</Data>
</Cell>
<Cell>
<Data ss:Type="String">Country</Data>
</Cell>
<Cell>
<Data ss:Type="String">Air Default</Data>
</Cell>
<Cell>
<Data ss:Type="String">Car Default</Data>
</Cell>
<Cell>
<Data ss:Type="String">Hotel Default</Data>
</Cell>
<Cell>
<Data ss:Type="String">Rail Default</Data>
</Cell>
<Cell>
<Data ss:Type="String">Taxi Default</Data>
</Cell>
<Cell>
<Data ss:Type="String">Allow Air</Data>
</Cell>
<Cell>
<Data ss:Type="String">Allow Car</Data>
</Cell>
<Cell>
<Data ss:Type="String">Allow Hotel</Data>
</Cell>
<Cell>
<Data ss:Type="String">Allow Rail</Data>
</Cell>
<Cell>
<Data ss:Type="String">Allow Limo</Data>
</Cell>
<Cell>
<Data ss:Type="String">Gov Card Type</Data>
</Cell>
<Cell>
<Data ss:Type="String">Status</Data>
</Cell>
<Cell>
<Data ss:Type="String">Error</Data>
</Cell>
</Row>
<Row ss:AutoFitHeight="0" ss:StyleID="s63">
<Cell>
<Data ss:Type="String">A000EmployeeID_0@google</Data>
</Cell>
<Cell>
<Data ss:Type="String">Corporate Amex</Data>
</Cell>
<Cell>
<Data ss:Type="String">AX</Data>
</Cell>
<Cell>
<Data ss:Type="String">CardmemberAcctNo0</Data>
</Cell>
<Cell>
<Data ss:Type="String">ExpireDate_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">FullName_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">Address_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">City_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">StateProvince_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">PostalCode_0</Data>
</Cell>
<Cell>
<Data ss:Type="String">US</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">0</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String">0</Data>
</Cell>
<Cell>
<Data ss:Type="String">0</Data>
</Cell>
<Cell>
<Data ss:Type="String">
0
</Data>
</Cell>
<Cell>
<Data ss:Type="String"/>
</Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Unsynced/>
<Print>
<ValidPrinterInfo/>
<HorizontalResolution>600</HorizontalResolution>
<VerticalResolution>600</VerticalResolution>
</Print>
<FreezePanes/>
<FrozenNoSplit/>
<SplitHorizontal>1</SplitHorizontal>
<TopRowBottomPane>1</TopRowBottomPane>
<ActivePane>2</ActivePane>
<Panes>
<Pane>
<Number>3</Number>
</Pane>
<Pane>
<Number>2</Number>
<ActiveRow>0</ActiveRow>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

 

----------------------------------------------------------------------------

 

for some reason, not able to attach the file.

Thanks,

Akshay

 

Show replies
Show replies
Answer
View Entire Topic
carolpereira0378_2
Explorer
‎03-06-2024 1:21 PM

Hello Akshay,

Can you use below expression in CPI and try once 

count(/*:Workbook/*:Worksheet[@*:Name='Credit Cards']/*:Table/*:Row)

I hope it works 🙂

Regards,

Carol Pereira

Show replies
Show replies
akshay108
Explorer
‎03-06-2024 1:38 PM
0 Kudos

Thank You ! This solution worked too 🙂

Ask a Question