Wednesday, 28 August 2013

XPath expression to create text delimited string

XPath expression to create text delimited string

I need to create a delimited string of the following format:
Stuff,Things;A,1;B,2
From the following XML Data:
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3"
x:FullColumns="1" x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Stuff</Data></Cell>
<Cell><Data ss:Type="String">Things</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">A</Data></Cell>
<Cell><Data ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">B</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
</Table>
Columns are denoted by , and rows are denoted by ;. This should create a
two dimensional array.
---------Notes-----------
I have tried the following expression against the sample XML but it does
not work when applied using NotePad++ XPatherizerNPP:
eval(eval(Row, 'concat(Cell, ";")'), "..")
The above expression throws the following erorr: XSLTContext is needed for
this query because of an unknown function
The "Eval" function typically works in MS InfoPath, but it is possible
that XPatherizer does not handle the XPath in the same way that InfoPath
does. It is also possible that I don't understand the context of this
statement and I am using it inappropriately.
As a sidenote, are there any good web resources that explain the use of
XPath Eval? I don't fully understand it and cannot seem to find any good
explanations.
--------Full XML--------
Here is the full body of the XML I am trying to use. It is an "XML
Spreadsheet" generated by MS Excel. I do not have the ability to create a
custom XML mapping as I do not have the rights to install the XML Tools on
my system.
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<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">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Author>Smith, Robert;-</Author>
<LastAuthor>Smith, Robert;-</LastAuthor>
<Created>2013-08-27T16:29:45Z</Created>
<Company>SmithWorks</Company>
<Version>14.00</Version>
</DocumentProperties>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>10035</WindowHeight>
<WindowWidth>22035</WindowWidth>
<WindowTopX>240</WindowTopX>
<WindowTopY>90</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:ID="Default" ss:Name="Normal">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font ss:FontName="Calibri" x:Family="Swiss" ss:Size="11"
ss:Color="#000000"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
</Styles>
<Worksheet ss:Name="Sheet1">
<Table ss:ExpandedColumnCount="2" ss:ExpandedRowCount="3" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
<Row>
<Cell><Data ss:Type="String">Stuff</Data></Cell>
<Cell><Data ss:Type="String">Things</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">A</Data></Cell>
<Cell><Data ss:Type="Number">1</Data></Cell>
</Row>
<Row>
<Cell><Data ss:Type="String">B</Data></Cell>
<Cell><Data ss:Type="Number">2</Data></Cell>
</Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<Selected/>
<Panes>
<Pane>
<Number>3</Number>
<ActiveRow>3</ActiveRow>
<ActiveCol>1</ActiveCol>
</Pane>
</Panes>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet2">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
<Worksheet ss:Name="Sheet3">
<Table ss:ExpandedColumnCount="1" ss:ExpandedRowCount="1" x:FullColumns="1"
x:FullRows="1" ss:DefaultRowHeight="15">
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<PageSetup>
<Header x:Margin="0.3"/>
<Footer x:Margin="0.3"/>
<PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
</PageSetup>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

No comments:

Post a Comment