using namespaces in xpath [LP8.6]

classic Classic list List threaded Threaded
14 messages Options
Reply | Threaded
Open this post in threaded view
|

using namespaces in xpath [LP8.6]

Jon Harris-2
Hi List

We are trying to extract data from an Excel spreadsheet, exported as XML.

<?LassoScript

var('thefileName') = '/uploads/data/sample.xml' ;
var('thefile') = file_read( var('thefileName')  ) ;
var('theXML') = xml(var('thefile')) ;

Var('Namespaces') = $theXML->(Namespaces);
Iterate( $Namespaces, Var('Temp') );
        $Temp->First + ' = ' + $Temp->Second "<br />" ;
/Iterate;

var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()') ;

?>

I can see the namespaces as my code returns:

= urn:schemas-microsoft-com:office:spreadsheet
o = urn:schemas-microsoft-com:office:office
x = urn:schemas-microsoft-com:office:excel
ss = urn:schemas-microsoft-com:office:spreadsheet


But my rows variable is always empty. I have tried lots of variations with the xpath including " //*:Row", but nothing seems to work. The data I want to extract is in the "ss" namespace (urn:schemas-microsoft-com:office:spreadsheet).

I can see that in xmlstream I can specify namespaces, but how do I use them within my xpath?

Any help appreciated.

Jon Harris


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Bil Corry-3
The few times I needed to pull data out of XML, I had a similar experience
where I couldn't get the xpath correct, and I'd give up and resort to regex.


- Bil

On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:

> Hi List
>
> We are trying to extract data from an Excel spreadsheet, exported as XML.
>
> <?LassoScript
>
> var('thefileName') = '/uploads/data/sample.xml' ;
> var('thefile') = file_read( var('thefileName')  ) ;
> var('theXML') = xml(var('thefile')) ;
>
> Var('Namespaces') = $theXML->(Namespaces);
> Iterate( $Namespaces, Var('Temp') );
>         $Temp->First + ' = ' + $Temp->Second "<br />" ;
> /Iterate;
>
> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
> ;
>
> ?>
>
> I can see the namespaces as my code returns:
>
> = urn:schemas-microsoft-com:office:spreadsheet
> o = urn:schemas-microsoft-com:office:office
> x = urn:schemas-microsoft-com:office:excel
> ss = urn:schemas-microsoft-com:office:spreadsheet
>
>
> But my rows variable is always empty. I have tried lots of variations with
> the xpath including " //*:Row", but nothing seems to work. The data I want
> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
> office:spreadsheet).
>
> I can see that in xmlstream I can specify namespaces, but how do I use
> them within my xpath?
>
> Any help appreciated.
>
> Jon Harris
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Mason Miller
Could you post an example of the xml?

Mason

Sent from my iPhone

> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>
> The few times I needed to pull data out of XML, I had a similar experience
> where I couldn't get the xpath correct, and I'd give up and resort to regex.
>
>
> - Bil
>
>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>
>> Hi List
>>
>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>
>> <?LassoScript
>>
>> var('thefileName') = '/uploads/data/sample.xml' ;
>> var('thefile') = file_read( var('thefileName')  ) ;
>> var('theXML') = xml(var('thefile')) ;
>>
>> Var('Namespaces') = $theXML->(Namespaces);
>> Iterate( $Namespaces, Var('Temp') );
>>        $Temp->First + ' = ' + $Temp->Second "<br />" ;
>> /Iterate;
>>
>> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>> ;
>>
>> ?>
>>
>> I can see the namespaces as my code returns:
>>
>> = urn:schemas-microsoft-com:office:spreadsheet
>> o = urn:schemas-microsoft-com:office:office
>> x = urn:schemas-microsoft-com:office:excel
>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>
>>
>> But my rows variable is always empty. I have tried lots of variations with
>> the xpath including " //*:Row", but nothing seems to work. The data I want
>> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>> office:spreadsheet).
>>
>> I can see that in xmlstream I can specify namespaces, but how do I use
>> them within my xpath?
>>
>> Any help appreciated.
>>
>> Jon Harris
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>>  the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Mason Miller
//*[name()='Row'][1]/*[name()='Cell'][1]/text()
Might work. Are you certain the value are looking for is PCDATA and not an attribute value?

Mason

Sent from my iPhone

> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>
> Could you post an example of the xml?
>
> Mason
>
> Sent from my iPhone
>
>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>
>> The few times I needed to pull data out of XML, I had a similar experience
>> where I couldn't get the xpath correct, and I'd give up and resort to regex.
>>
>>
>> - Bil
>>
>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>
>>> Hi List
>>>
>>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>
>>> <?LassoScript
>>>
>>> var('thefileName') = '/uploads/data/sample.xml' ;
>>> var('thefile') = file_read( var('thefileName')  ) ;
>>> var('theXML') = xml(var('thefile')) ;
>>>
>>> Var('Namespaces') = $theXML->(Namespaces);
>>> Iterate( $Namespaces, Var('Temp') );
>>>       $Temp->First + ' = ' + $Temp->Second "<br />" ;
>>> /Iterate;
>>>
>>> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>> ;
>>>
>>> ?>
>>>
>>> I can see the namespaces as my code returns:
>>>
>>> = urn:schemas-microsoft-com:office:spreadsheet
>>> o = urn:schemas-microsoft-com:office:office
>>> x = urn:schemas-microsoft-com:office:excel
>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>
>>>
>>> But my rows variable is always empty. I have tried lots of variations with
>>> the xpath including " //*:Row", but nothing seems to work. The data I want
>>> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>>> office:spreadsheet).
>>>
>>> I can see that in xmlstream I can specify namespaces, but how do I use
>>> them within my xpath?
>>>
>>> Any help appreciated.
>>>
>>> Jon Harris
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Mason Miller
In reply to this post by Mason Miller
I bet your getting a nose set and not a single node. Need the xml.

Mason

Sent from my iPhone

> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>
> Could you post an example of the xml?
>
> Mason
>
> Sent from my iPhone
>
>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>
>> The few times I needed to pull data out of XML, I had a similar experience
>> where I couldn't get the xpath correct, and I'd give up and resort to regex.
>>
>>
>> - Bil
>>
>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>
>>> Hi List
>>>
>>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>
>>> <?LassoScript
>>>
>>> var('thefileName') = '/uploads/data/sample.xml' ;
>>> var('thefile') = file_read( var('thefileName')  ) ;
>>> var('theXML') = xml(var('thefile')) ;
>>>
>>> Var('Namespaces') = $theXML->(Namespaces);
>>> Iterate( $Namespaces, Var('Temp') );
>>>       $Temp->First + ' = ' + $Temp->Second "<br />" ;
>>> /Iterate;
>>>
>>> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>> ;
>>>
>>> ?>
>>>
>>> I can see the namespaces as my code returns:
>>>
>>> = urn:schemas-microsoft-com:office:spreadsheet
>>> o = urn:schemas-microsoft-com:office:office
>>> x = urn:schemas-microsoft-com:office:excel
>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>
>>>
>>> But my rows variable is always empty. I have tried lots of variations with
>>> the xpath including " //*:Row", but nothing seems to work. The data I want
>>> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>>> office:spreadsheet).
>>>
>>> I can see that in xmlstream I can specify namespaces, but how do I use
>>> them within my xpath?
>>>
>>> Any help appreciated.
>>>
>>> Jon Harris
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Mason Miller
In reply to this post by Mason Miller
> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()


Will match on your namespace.

Sent from my iPhone

> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>
> //*[name()='Row'][1]/*[name()='Cell'][1]/text()
> Might work. Are you certain the value are looking for is PCDATA and not an attribute value?
>
> Mason
>
> Sent from my iPhone
>
>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>
>> Could you post an example of the xml?
>>
>> Mason
>>
>> Sent from my iPhone
>>
>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>
>>> The few times I needed to pull data out of XML, I had a similar experience
>>> where I couldn't get the xpath correct, and I'd give up and resort to regex.
>>>
>>>
>>> - Bil
>>>
>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>
>>>> Hi List
>>>>
>>>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>>
>>>> <?LassoScript
>>>>
>>>> var('thefileName') = '/uploads/data/sample.xml' ;
>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>> var('theXML') = xml(var('thefile')) ;
>>>>
>>>> Var('Namespaces') = $theXML->(Namespaces);
>>>> Iterate( $Namespaces, Var('Temp') );
>>>>      $Temp->First + ' = ' + $Temp->Second "<br />" ;
>>>> /Iterate;
>>>>
>>>> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>> ;
>>>>
>>>> ?>
>>>>
>>>> I can see the namespaces as my code returns:
>>>>
>>>> = urn:schemas-microsoft-com:office:spreadsheet
>>>> o = urn:schemas-microsoft-com:office:office
>>>> x = urn:schemas-microsoft-com:office:excel
>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>
>>>>
>>>> But my rows variable is always empty. I have tried lots of variations with
>>>> the xpath including " //*:Row", but nothing seems to work. The data I want
>>>> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>>>> office:spreadsheet).
>>>>
>>>> I can see that in xmlstream I can specify namespaces, but how do I use
>>>> them within my xpath?
>>>>
>>>> Any help appreciated.
>>>>
>>>> Jon Harris
>>>>
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to
>>>> the mailing list Lasso [hidden email]
>>>> Official list archives available at http://www.lassotalk.com
>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Mason Miller
One final thought is that if there is more than one occurrence in the document, you need to iterate through those.

Mason

Sent from my iPhone

On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:

>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>
>
> Will match on your namespace.
>
> Sent from my iPhone
>
>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>
>> //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>> Might work. Are you certain the value are looking for is PCDATA and not an attribute value?
>>
>> Mason
>>
>> Sent from my iPhone
>>
>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>
>>> Could you post an example of the xml?
>>>
>>> Mason
>>>
>>> Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>
>>>> The few times I needed to pull data out of XML, I had a similar experience
>>>> where I couldn't get the xpath correct, and I'd give up and resort to regex.
>>>>
>>>>
>>>> - Bil
>>>>
>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>
>>>>> Hi List
>>>>>
>>>>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>>>
>>>>> <?LassoScript
>>>>>
>>>>> var('thefileName') = '/uploads/data/sample.xml' ;
>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>
>>>>> Var('Namespaces') = $theXML->(Namespaces);
>>>>> Iterate( $Namespaces, Var('Temp') );
>>>>>     $Temp->First + ' = ' + $Temp->Second "<br />" ;
>>>>> /Iterate;
>>>>>
>>>>> var('theRows') = xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>> ;
>>>>>
>>>>> ?>
>>>>>
>>>>> I can see the namespaces as my code returns:
>>>>>
>>>>> = urn:schemas-microsoft-com:office:spreadsheet
>>>>> o = urn:schemas-microsoft-com:office:office
>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>
>>>>>
>>>>> But my rows variable is always empty. I have tried lots of variations with
>>>>> the xpath including " //*:Row", but nothing seems to work. The data I want
>>>>> to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>>>>> office:spreadsheet).
>>>>>
>>>>> I can see that in xmlstream I can specify namespaces, but how do I use
>>>>> them within my xpath?
>>>>>
>>>>> Any help appreciated.
>>>>>
>>>>> Jon Harris
>>>>>
>>>>>
>>>>> #############################################################
>>>>>
>>>>> This message is sent to you because you are subscribed to
>>>>> the mailing list Lasso [hidden email]
>>>>> Official list archives available at http://www.lassotalk.com
>>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>>> Send administrative queries to  <[hidden email]>
>>>>>
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to
>>>> the mailing list Lasso [hidden email]
>>>> Official list archives available at http://www.lassotalk.com
>>>> To unsubscribe, E-mail to: <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to
>>> the mailing list Lasso [hidden email]
>>> Official list archives available at http://www.lassotalk.com
>>> To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>  the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: using namespaces in xpath [LP8.6]

Jon Harris-2
Hi List

Thanks to everyone who replied over the weekend.  Here is a sample of the data, I'm just showing 2 dummy rows below, as we have a couple of hundred of real records in the live system.

Apart from the namespace issue, Excel does seem to create reasonable XML.

As I mentioned in my post, I just want to be able to read the individual rows/cells. I've tried using an online xPath generator, then pasting that into my lasso xPath and even that doesn't work.

I think I might have to use Bil Corry's suggestion of RegEx'ing the data.

Regards
Jon Harris



<?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>Jon Harris</Author>
  <LastAuthor>Jon Harris</LastAuthor>
  <Created>2017-06-01T07:37:47Z</Created>
  <Version>16.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>9855</WindowHeight>
  <WindowWidth>27975</WindowWidth>
  <WindowTopX>0</WindowTopX>
  <WindowTopY>0</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="EBlast user data.">
  <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="47" x:FullColumns="1"
   x:FullRows="1" ss:DefaultRowHeight="15">
   <Row>
    <Cell><Data ss:Type="String">Title</Data></Cell>
    <Cell><Data ss:Type="String">Forename</Data></Cell>
    <Cell><Data ss:Type="String">Surname</Data></Cell>
    <Cell><Data ss:Type="String">Postcode</Data></Cell>
    <Cell><Data ss:Type="String">Email</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Mrs</Data></Cell>
    <Cell><Data ss:Type="String">Jane</Data></Cell>
    <Cell><Data ss:Type="String">Doe</Data></Cell>
    <Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell>
    <Cell><Data ss:Type="String">[hidden email]</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">Mr</Data></Cell>
    <Cell><Data ss:Type="String">John</Data></Cell>
    <Cell><Data ss:Type="String">Doe</Data></Cell>
    <Cell><Data ss:Type="String">AB1 2CD</Data></Cell>
    <Cell><Data ss:Type="String">[hidden email]</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/>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Mason Miller
Sent: 03 June 2017 12:35 AM
To: [hidden email]
Subject: Re: using namespaces in xpath [LP8.6]

One final thought is that if there is more than one occurrence in the document, you need to iterate through those.

Mason

Sent from my iPhone

On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:

>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>
>
> Will match on your namespace.
>
> Sent from my iPhone
>
>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>
>> //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>> Might work. Are you certain the value are looking for is PCDATA and not an attribute value?
>>
>> Mason
>>
>> Sent from my iPhone
>>
>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>
>>> Could you post an example of the xml?
>>>
>>> Mason
>>>
>>> Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>
>>>> The few times I needed to pull data out of XML, I had a similar
>>>> experience where I couldn't get the xpath correct, and I'd give up and resort to regex.
>>>>
>>>>
>>>> - Bil
>>>>
>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>
>>>>> Hi List
>>>>>
>>>>> We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>>>
>>>>> <?LassoScript
>>>>>
>>>>> var('thefileName') = '/uploads/data/sample.xml' ;
>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>
>>>>> Var('Namespaces') = $theXML->(Namespaces); Iterate( $Namespaces,
>>>>> Var('Temp') );
>>>>>     $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
>>>>>
>>>>> var('theRows') =
>>>>> xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>> ;
>>>>>
>>>>> ?>
>>>>>
>>>>> I can see the namespaces as my code returns:
>>>>>
>>>>> = urn:schemas-microsoft-com:office:spreadsheet
>>>>> o = urn:schemas-microsoft-com:office:office
>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>
>>>>>
>>>>> But my rows variable is always empty. I have tried lots of
>>>>> variations with the xpath including " //*:Row", but nothing seems
>>>>> to work. The data I want to extract is in the "ss" namespace (urn:schemas-microsoft-com:
>>>>> office:spreadsheet).
>>>>>
>>>>> I can see that in xmlstream I can specify namespaces, but how do I
>>>>> use them within my xpath?
>>>>>
>>>>> Any help appreciated.
>>>>>
>>>>> Jon Harris
>>>>>
>>>>>
>>>>> #############################################################
>>>>>
>>>>> This message is sent to you because you are subscribed to the
>>>>> mailing list Lasso [hidden email] Official list
>>>>> archives available at http://www.lassotalk.com To unsubscribe,
>>>>> E-mail to: <[hidden email]>
>>>>> Send administrative queries to  
>>>>> <[hidden email]>
>>>>>
>>>>
>>>> #############################################################
>>>>
>>>> This message is sent to you because you are subscribed to the
>>>> mailing list Lasso [hidden email] Official list archives
>>>> available at http://www.lassotalk.com To unsubscribe, E-mail to:
>>>> <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to the
>>> mailing list Lasso [hidden email] Official list archives
>>> available at http://www.lassotalk.com To unsubscribe, E-mail to:
>>> <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to the mailing
>> list Lasso [hidden email] Official list archives available
>> at http://www.lassotalk.com To unsubscribe, E-mail to:
>> <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to  the mailing
> list Lasso [hidden email] Official list archives available
> at http://www.lassotalk.com To unsubscribe, E-mail to:
> <[hidden email]>
> Send administrative queries to  <[hidden email]>



#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: using namespaces in xpath [LP8.6]

stevepiercy
I've never been able to use XML with 'xmlns' in the data, so a
regex that replaces it with '', then casting it to an xml type
has worked for me.

See:
http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.html

--steve


On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:

>Hi List
>
>Thanks to everyone who replied over the weekend.  Here is a
>sample of the data, I'm just showing 2 dummy rows below, as we
>have a couple of hundred of real records in the live system.
>
>Apart from the namespace issue, Excel does seem to create reasonable XML.
>
>As I mentioned in my post, I just want to be able to read the
>individual rows/cells. I've tried using an online xPath
>generator, then pasting that into my lasso xPath and even that
>doesn't work.
>
>I think I might have to use Bil Corry's suggestion of RegEx'ing
>the data.
>Regards
>Jon Harris
>
>
>
><?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>Jon Harris</Author>
><LastAuthor>Jon Harris</LastAuthor>
><Created>2017-06-01T07:37:47Z</Created>
><Version>16.00</Version>
></DocumentProperties>
><OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
><AllowPNG/>
></OfficeDocumentSettings>
><ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
><WindowHeight>9855</WindowHeight>
><WindowWidth>27975</WindowWidth>
><WindowTopX>0</WindowTopX>
><WindowTopY>0</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="EBlast user data.">
><Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="47" x:FullColumns="1"
>x:FullRows="1" ss:DefaultRowHeight="15">
><Row>
><Cell><Data ss:Type="String">Title</Data></Cell>
><Cell><Data ss:Type="String">Forename</Data></Cell>
><Cell><Data ss:Type="String">Surname</Data></Cell>
><Cell><Data ss:Type="String">Postcode</Data></Cell>
><Cell><Data ss:Type="String">Email</Data></Cell>
></Row>
><Row>
><Cell><Data ss:Type="String">Mrs</Data></Cell>
><Cell><Data ss:Type="String">Jane</Data></Cell>
><Cell><Data ss:Type="String">Doe</Data></Cell>
><Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell>
><Cell><Data ss:Type="String">[hidden email]</Data></Cell>
></Row>
><Row>
><Cell><Data ss:Type="String">Mr</Data></Cell>
><Cell><Data ss:Type="String">John</Data></Cell>
><Cell><Data ss:Type="String">Doe</Data></Cell>
><Cell><Data ss:Type="String">AB1 2CD</Data></Cell>
><Cell><Data ss:Type="String">[hidden email]</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/>
><ProtectObjects>False</ProtectObjects>
><ProtectScenarios>False</ProtectScenarios>
></WorksheetOptions>
></Worksheet>
></Workbook>
>
>
>-----Original Message-----
>From: [hidden email]
>[mailto:[hidden email]] On Behalf Of Mason Miller
>Sent: 03 June 2017 12:35 AM
>To: [hidden email]
>Subject: Re: using namespaces in xpath [LP8.6]
>
>One final thought is that if there is more than one occurrence
>in the document, you need to iterate through those.
>Mason
>
>Sent from my iPhone
>
>On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
>
>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>>
>>
>>Will match on your namespace.
>>Sent from my iPhone
>>
>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>>   //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>>> Might work. Are you certain the value are looking for is PCDATA and not an attribute
>value?
>>>   Mason
>>>   Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>>   Could you post an example of the xml?
>>>>   Mason
>>>>   Sent from my iPhone
>>>>
>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>>   The few times I needed to pull data out of XML, I had a
>>>>>similar  experience where I couldn't get the xpath correct,
>>>>>and I'd give up and resort to
>regex.
>>>>>    - Bil
>>>>>
>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>>   Hi List
>>>>>>   We are trying to extract data from an Excel
>>>>>>spreadsheet, exported as XML.
>>>>>>   <?LassoScript
>>>>>>   var('thefileName') = '/uploads/data/sample.xml' ;
>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>>   Var('Namespaces') = $theXML->(Namespaces); Iterate(
>>>>>>$Namespaces,  Var('Temp') );
>>>>>>     $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
>>>>>>   var('theRows') =  xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>>> ;
>>>>>>   ?>
>>>>>>   I can see the namespaces as my code returns:
>>>>>>   = urn:schemas-microsoft-com:office:spreadsheet
>>>>>> o = urn:schemas-microsoft-com:office:office
>>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>    But my rows variable is always empty. I have tried
>>>>>>lots of  variations with the xpath including " //*:Row",
>>>>>>but nothing seems  to work. The data I want to extract is
>>>>>>in the "ss" namespace
>(urn:schemas-microsoft-com:
>>>>>> office:spreadsheet).
>>>>>>   I can see that in xmlstream I can specify namespaces,
>>>>>>but how do I  use them within my xpath?
>>>>>>   Any help appreciated.
>>>>>>   Jon Harris
>>>>>>    #############################################################
>>>>>>   This message is sent to you because you are subscribed
>>>>>>to the  mailing list Lasso [hidden email]
>>>>>>Official list  archives available at
>>>>>>http://www.lassotalk.com To unsubscribe,  E-mail to: <[hidden email]>
>>>>>>  Send administrative queries to   <[hidden email]>
>>>>>>
>>>>>   #############################################################
>>>>>   This message is sent to you because you are subscribed
>>>>>to the  mailing list Lasso [hidden email]
>>>>>Official list archives  available at
>>>>>http://www.lassotalk.com To unsubscribe, E-mail to:  <[hidden email]>
>>>>> Send administrative queries to  <[hidden email]>
>>>>     #############################################################
>>>>   This message is sent to you because you are subscribed to
>>>>the  mailing list Lasso [hidden email] Official
>>>>list archives  available at http://www.lassotalk.com To
>>>>unsubscribe, E-mail to:  <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>   #############################################################
>>>   This message is sent to you because you are subscribed to
>>>the mailing  list Lasso [hidden email] Official
>>>list archives available  at http://www.lassotalk.com To
>>>unsubscribe, E-mail to:  <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>#############################################################
>>
>>This message is sent to you because you are subscribed to  the
>>mailing list Lasso [hidden email] Official list
>>archives available at http://www.lassotalk.com To unsubscribe,
>>E-mail to: <[hidden email]>
>>Send administrative queries to  <[hidden email]>
>
>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email] Official list
>archives available at http://www.lassotalk.com To unsubscribe,
>E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>
>
>#############################################################
>
>This message is sent to you because you are subscribed to
>the mailing list Lasso [hidden email]
>Official list archives available at http://www.lassotalk.com
>To unsubscribe, E-mail to: <[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.stevepiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Andreas Steinmann-2
I'm in a situation where I have to extract xml data regularly and update a local database updating old and adding new records. Couldn't find a way with lasso but came across red bean PHP
http://redbeanphp.com/index.php which does the trick for me every 30 mins pulling the xml from a distant server, creating tables, fields, dependencies when needed and updating the data. I'm running it as a cron job from the command line. Than I get a nice MySQL database and continue working with lasso.

All the best,
Andreas



Andreas Steinmann‌
-------------------------------
Egebjerghuse 2
DK 2750 Ballerup
Denmark

Quartier Meran
F 06540 Saorge
France
-------------------------------
[hidden email]
+45 28117505

> On 5 Jun 2017, at 10.34, Steve Piercy - Website Builder <[hidden email]> wrote:
>
> I've never been able to use XML with 'xmlns' in the data, so a regex that replaces it with '', then casting it to an xml type has worked for me.
>
> See:
> http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.html
>
> --steve
>
>
> On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:
>
>> Hi List
>>
>> Thanks to everyone who replied over the weekend.  Here is a sample of the data, I'm just showing 2 dummy rows below, as we have a couple of hundred of real records in the live system.
>>
>> Apart from the namespace issue, Excel does seem to create reasonable XML.
>>
>> As I mentioned in my post, I just want to be able to read the individual rows/cells. I've tried using an online xPath generator, then pasting that into my lasso xPath and even that doesn't work.
>>
>> I think I might have to use Bil Corry's suggestion of RegEx'ing the data.
>> Regards
>> Jon Harris
>>
>>
>>
>> <?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>Jon Harris</Author>
>> <LastAuthor>Jon Harris</LastAuthor>
>> <Created>2017-06-01T07:37:47Z</Created>
>> <Version>16.00</Version>
>> </DocumentProperties>
>> <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
>> <AllowPNG/>
>> </OfficeDocumentSettings>
>> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
>> <WindowHeight>9855</WindowHeight>
>> <WindowWidth>27975</WindowWidth>
>> <WindowTopX>0</WindowTopX>
>> <WindowTopY>0</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="EBlast user data.">
>> <Table ss:ExpandedColumnCount="5" ss:ExpandedRowCount="47" x:FullColumns="1"
>> x:FullRows="1" ss:DefaultRowHeight="15">
>> <Row>
>> <Cell><Data ss:Type="String">Title</Data></Cell>
>> <Cell><Data ss:Type="String">Forename</Data></Cell>
>> <Cell><Data ss:Type="String">Surname</Data></Cell>
>> <Cell><Data ss:Type="String">Postcode</Data></Cell>
>> <Cell><Data ss:Type="String">Email</Data></Cell>
>> </Row>
>> <Row>
>> <Cell><Data ss:Type="String">Mrs</Data></Cell>
>> <Cell><Data ss:Type="String">Jane</Data></Cell>
>> <Cell><Data ss:Type="String">Doe</Data></Cell>
>> <Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell>
>> <Cell><Data ss:Type="String">[hidden email]</Data></Cell>
>> </Row>
>> <Row>
>> <Cell><Data ss:Type="String">Mr</Data></Cell>
>> <Cell><Data ss:Type="String">John</Data></Cell>
>> <Cell><Data ss:Type="String">Doe</Data></Cell>
>> <Cell><Data ss:Type="String">AB1 2CD</Data></Cell>
>> <Cell><Data ss:Type="String">[hidden email]</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/>
>> <ProtectObjects>False</ProtectObjects>
>> <ProtectScenarios>False</ProtectScenarios>
>> </WorksheetOptions>
>> </Worksheet>
>> </Workbook>
>>
>>
>> -----Original Message-----
>> From: [hidden email] [mailto:[hidden email]] On Behalf Of Mason Miller
>> Sent: 03 June 2017 12:35 AM
>> To: [hidden email]
>> Subject: Re: using namespaces in xpath [LP8.6]
>>
>> One final thought is that if there is more than one occurrence in the document, you need to iterate through those.
>> Mason
>>
>> Sent from my iPhone
>>
>> On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
>>
>>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>>>
>>>
>>> Will match on your namespace.
>>> Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>>>  //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>>>> Might work. Are you certain the value are looking for is PCDATA and not an attribute
>> value?
>>>>  Mason
>>>>  Sent from my iPhone
>>>>
>>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>>>  Could you post an example of the xml?
>>>>>  Mason
>>>>>  Sent from my iPhone
>>>>>
>>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>>>  The few times I needed to pull data out of XML, I had a similar  experience where I couldn't get the xpath correct, and I'd give up and resort to
>> regex.
>>>>>>   - Bil
>>>>>>
>>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>>>  Hi List
>>>>>>>  We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>>>>>  <?LassoScript
>>>>>>>  var('thefileName') = '/uploads/data/sample.xml' ;
>>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>>>  Var('Namespaces') = $theXML->(Namespaces); Iterate( $Namespaces,  Var('Temp') );
>>>>>>>    $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
>>>>>>>  var('theRows') =  xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>>>> ;
>>>>>>>  ?>
>>>>>>>  I can see the namespaces as my code returns:
>>>>>>>  = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>> o = urn:schemas-microsoft-com:office:office
>>>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>>   But my rows variable is always empty. I have tried lots of  variations with the xpath including " //*:Row", but nothing seems  to work. The data I want to extract is in the "ss" namespace
>> (urn:schemas-microsoft-com:
>>>>>>> office:spreadsheet).
>>>>>>>  I can see that in xmlstream I can specify namespaces, but how do I  use them within my xpath?
>>>>>>>  Any help appreciated.
>>>>>>>  Jon Harris
>>>>>>>   #############################################################
>>>>>>>  This message is sent to you because you are subscribed to the  mailing list Lasso [hidden email] Official list  archives available at http://www.lassotalk.com To unsubscribe,  E-mail to: <[hidden email]>
>>>>>>> Send administrative queries to   <[hidden email]>
>>>>>>>
>>>>>>  #############################################################
>>>>>>  This message is sent to you because you are subscribed to the  mailing list Lasso [hidden email] Official list archives  available at http://www.lassotalk.com To unsubscribe, E-mail to:  <[hidden email]>
>>>>>> Send administrative queries to  <[hidden email]>
>>>>>    #############################################################
>>>>>  This message is sent to you because you are subscribed to the  mailing list Lasso [hidden email] Official list archives  available at http://www.lassotalk.com To unsubscribe, E-mail to:  <[hidden email]>
>>>>> Send administrative queries to  <[hidden email]>
>>>>  #############################################################
>>>>  This message is sent to you because you are subscribed to the mailing  list Lasso [hidden email] Official list archives available  at http://www.lassotalk.com To unsubscribe, E-mail to:  <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to
>> the mailing list Lasso [hidden email]
>> Official list archives available at http://www.lassotalk.com
>> To unsubscribe, E-mail to: <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.stevepiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
> the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: using namespaces in xpath [LP8.6]

Jon Harris-2
In reply to this post by stevepiercy
Hi Steve

Thanks for the link. Oddly enough that was my post back in 2007 (different context). I do feel old!

I didn't relate this to the xmlns content as I was able to read the name spaces.

I will give it a go.

Regards
Jon


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Steve Piercy - Website Builder
Sent: 05 June 2017 09:34 AM
To: [hidden email]
Subject: RE: using namespaces in xpath [LP8.6]

I've never been able to use XML with 'xmlns' in the data, so a regex that replaces it with '', then casting it to an xml type has worked for me.

See:
http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.html

--steve


On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:

>Hi List
>
>Thanks to everyone who replied over the weekend.  Here is a sample of
>the data, I'm just showing 2 dummy rows below, as we have a couple of
>hundred of real records in the live system.
>
>Apart from the namespace issue, Excel does seem to create reasonable XML.
>
>As I mentioned in my post, I just want to be able to read the
>individual rows/cells. I've tried using an online xPath generator, then
>pasting that into my lasso xPath and even that doesn't work.
>
>I think I might have to use Bil Corry's suggestion of RegEx'ing the
>data.
>Regards
>Jon Harris
>
>
>
><?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>Jon Harris</Author>
><LastAuthor>Jon Harris</LastAuthor>
><Created>2017-06-01T07:37:47Z</Created>
><Version>16.00</Version>
></DocumentProperties>
><OfficeDocumentSettings
>xmlns="urn:schemas-microsoft-com:office:office">
><AllowPNG/>
></OfficeDocumentSettings>
><ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
><WindowHeight>9855</WindowHeight>
><WindowWidth>27975</WindowWidth>
><WindowTopX>0</WindowTopX>
><WindowTopY>0</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="EBlast user data."> <Table ss:ExpandedColumnCount="5"
>ss:ExpandedRowCount="47" x:FullColumns="1"
>x:FullRows="1" ss:DefaultRowHeight="15"> <Row> <Cell><Data
>ss:Type="String">Title</Data></Cell>
><Cell><Data ss:Type="String">Forename</Data></Cell>
><Cell><Data ss:Type="String">Surname</Data></Cell>
><Cell><Data ss:Type="String">Postcode</Data></Cell>
><Cell><Data ss:Type="String">Email</Data></Cell>
></Row>
><Row>
><Cell><Data ss:Type="String">Mrs</Data></Cell>
><Cell><Data ss:Type="String">Jane</Data></Cell>
><Cell><Data ss:Type="String">Doe</Data></Cell>
><Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell> <Cell><Data
>ss:Type="String">[hidden email]</Data></Cell>
></Row>
><Row>
><Cell><Data ss:Type="String">Mr</Data></Cell> <Cell><Data
>ss:Type="String">John</Data></Cell>
><Cell><Data ss:Type="String">Doe</Data></Cell>
><Cell><Data ss:Type="String">AB1 2CD</Data></Cell> <Cell><Data
>ss:Type="String">[hidden email]</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/> <ProtectObjects>False</ProtectObjects>
><ProtectScenarios>False</ProtectScenarios>
></WorksheetOptions>
></Worksheet>
></Workbook>
>
>
>-----Original Message-----
>From: [hidden email]
>[mailto:[hidden email]] On Behalf Of Mason Miller
>Sent: 03 June 2017 12:35 AM
>To: [hidden email]
>Subject: Re: using namespaces in xpath [LP8.6]
>
>One final thought is that if there is more than one occurrence in the
>document, you need to iterate through those.
>Mason
>
>Sent from my iPhone
>
>On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
>
>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>>
>>
>>Will match on your namespace.
>>Sent from my iPhone
>>
>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>>   //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>>> Might work. Are you certain the value are looking for is PCDATA and
>>> not an attribute
>value?
>>>   Mason
>>>   Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>>   Could you post an example of the xml?
>>>>   Mason
>>>>   Sent from my iPhone
>>>>
>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>>   The few times I needed to pull data out of XML, I had a similar  
>>>>>experience where I couldn't get the xpath correct, and I'd give up
>>>>>and resort to
>regex.
>>>>>    - Bil
>>>>>
>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>>   Hi List
>>>>>>   We are trying to extract data from an Excel spreadsheet,
>>>>>>exported as XML.
>>>>>>   <?LassoScript
>>>>>>   var('thefileName') = '/uploads/data/sample.xml' ;
>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>>   Var('Namespaces') = $theXML->(Namespaces); Iterate(
>>>>>>$Namespaces,  Var('Temp') );
>>>>>>     $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
>>>>>>   var('theRows') =  
>>>>>>xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>>> ;
>>>>>>   ?>
>>>>>>   I can see the namespaces as my code returns:
>>>>>>   = urn:schemas-microsoft-com:office:spreadsheet
>>>>>> o = urn:schemas-microsoft-com:office:office
>>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>    But my rows variable is always empty. I have tried lots of  
>>>>>>variations with the xpath including " //*:Row", but nothing seems  
>>>>>>to work. The data I want to extract is in the "ss" namespace
>(urn:schemas-microsoft-com:
>>>>>> office:spreadsheet).
>>>>>>   I can see that in xmlstream I can specify namespaces, but how
>>>>>>do I  use them within my xpath?
>>>>>>   Any help appreciated.
>>>>>>   Jon Harris
>>>>>>    #############################################################
>>>>>>   This message is sent to you because you are subscribed to the  
>>>>>>mailing list Lasso [hidden email] Official list  
>>>>>>archives available at http://www.lassotalk.com To unsubscribe,  
>>>>>>E-mail to: <[hidden email]>
>>>>>>  Send administrative queries to   <[hidden email]>
>>>>>>
>>>>>   #############################################################
>>>>>   This message is sent to you because you are subscribed to the  
>>>>>mailing list Lasso [hidden email] Official list archives  
>>>>>available at http://www.lassotalk.com To unsubscribe, E-mail to:  
>>>>><[hidden email]>
>>>>> Send administrative queries to  
>>>>><[hidden email]>
>>>>     #############################################################
>>>>   This message is sent to you because you are subscribed to the  
>>>>mailing list Lasso [hidden email] Official list archives  
>>>>available at http://www.lassotalk.com To unsubscribe, E-mail to:  
>>>><[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>   #############################################################
>>>   This message is sent to you because you are subscribed to the
>>>mailing  list Lasso [hidden email] Official list archives
>>>available  at http://www.lassotalk.com To unsubscribe, E-mail to:  
>>><[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>#############################################################
>>
>>This message is sent to you because you are subscribed to  the mailing
>>list Lasso [hidden email] Official list archives available
>>at http://www.lassotalk.com To unsubscribe, E-mail to:
>><[hidden email]>
>>Send administrative queries to  <[hidden email]>
>
>
>
>#############################################################
>
>This message is sent to you because you are subscribed to the mailing
>list Lasso [hidden email] Official list archives available
>at http://www.lassotalk.com To unsubscribe, E-mail to:
><[hidden email]>
>Send administrative queries to  <[hidden email]>
>
>#############################################################
>
>This message is sent to you because you are subscribed to the mailing
>list Lasso [hidden email] Official list archives available
>at http://www.lassotalk.com To unsubscribe, E-mail to:
><[hidden email]>
>Send administrative queries to  <[hidden email]>

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Steve Piercy              Website Builder              Soquel, CA
<[hidden email]>               <http://www.stevepiercy.com/>


#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: using namespaces in xpath [LP8.6]

Jon Harris-2
In reply to this post by Andreas Steinmann-2
Hi Andreas

Thanks for your post, but I think I have a solution using native Lasso. I will keep RedBean in my bookmarks.

Regards
Jon


-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Andreas Steinmann
Sent: 05 June 2017 09:53 AM
To: [hidden email]
Subject: Re: using namespaces in xpath [LP8.6]

I'm in a situation where I have to extract xml data regularly and update a local database updating old and adding new records. Couldn't find a way with lasso but came across red bean PHP http://redbeanphp.com/index.php which does the trick for me every 30 mins pulling the xml from a distant server, creating tables, fields, dependencies when needed and updating the data. I'm running it as a cron job from the command line. Than I get a nice MySQL database and continue working with lasso.

All the best,
Andreas



Andreas Steinmann‌
-------------------------------
Egebjerghuse 2
DK 2750 Ballerup
Denmark

Quartier Meran
F 06540 Saorge
France
-------------------------------
[hidden email]
+45 28117505

> On 5 Jun 2017, at 10.34, Steve Piercy - Website Builder <[hidden email]> wrote:
>
> I've never been able to use XML with 'xmlns' in the data, so a regex that replaces it with '', then casting it to an xml type has worked for me.
>
> See:
> http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.ht
> ml
>
> --steve
>
>
> On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:
>
>> Hi List
>>
>> Thanks to everyone who replied over the weekend.  Here is a sample of the data, I'm just showing 2 dummy rows below, as we have a couple of hundred of real records in the live system.
>>
>> Apart from the namespace issue, Excel does seem to create reasonable XML.
>>
>> As I mentioned in my post, I just want to be able to read the individual rows/cells. I've tried using an online xPath generator, then pasting that into my lasso xPath and even that doesn't work.
>>
>> I think I might have to use Bil Corry's suggestion of RegEx'ing the data.
>> Regards
>> Jon Harris
>>
>>
>>
>> <?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>Jon Harris</Author>
>> <LastAuthor>Jon Harris</LastAuthor>
>> <Created>2017-06-01T07:37:47Z</Created>
>> <Version>16.00</Version>
>> </DocumentProperties>
>> <OfficeDocumentSettings
>> xmlns="urn:schemas-microsoft-com:office:office">
>> <AllowPNG/>
>> </OfficeDocumentSettings>
>> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
>> <WindowHeight>9855</WindowHeight>
>> <WindowWidth>27975</WindowWidth>
>> <WindowTopX>0</WindowTopX>
>> <WindowTopY>0</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="EBlast user data."> <Table ss:ExpandedColumnCount="5"
>> ss:ExpandedRowCount="47" x:FullColumns="1"
>> x:FullRows="1" ss:DefaultRowHeight="15"> <Row> <Cell><Data
>> ss:Type="String">Title</Data></Cell>
>> <Cell><Data ss:Type="String">Forename</Data></Cell>
>> <Cell><Data ss:Type="String">Surname</Data></Cell>
>> <Cell><Data ss:Type="String">Postcode</Data></Cell>
>> <Cell><Data ss:Type="String">Email</Data></Cell>
>> </Row>
>> <Row>
>> <Cell><Data ss:Type="String">Mrs</Data></Cell>
>> <Cell><Data ss:Type="String">Jane</Data></Cell>
>> <Cell><Data ss:Type="String">Doe</Data></Cell>
>> <Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell> <Cell><Data
>> ss:Type="String">[hidden email]</Data></Cell>
>> </Row>
>> <Row>
>> <Cell><Data ss:Type="String">Mr</Data></Cell> <Cell><Data
>> ss:Type="String">John</Data></Cell>
>> <Cell><Data ss:Type="String">Doe</Data></Cell>
>> <Cell><Data ss:Type="String">AB1 2CD</Data></Cell> <Cell><Data
>> ss:Type="String">[hidden email]</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/>
>> <ProtectObjects>False</ProtectObjects>
>> <ProtectScenarios>False</ProtectScenarios>
>> </WorksheetOptions>
>> </Worksheet>
>> </Workbook>
>>
>>
>> -----Original Message-----
>> From: [hidden email]
>> [mailto:[hidden email]] On Behalf Of Mason Miller
>> Sent: 03 June 2017 12:35 AM
>> To: [hidden email]
>> Subject: Re: using namespaces in xpath [LP8.6]
>>
>> One final thought is that if there is more than one occurrence in the document, you need to iterate through those.
>> Mason
>>
>> Sent from my iPhone
>>
>> On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
>>
>>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
>>>
>>>
>>> Will match on your namespace.
>>> Sent from my iPhone
>>>
>>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]> wrote:
>>>>  //*[name()='Row'][1]/*[name()='Cell'][1]/text()
>>>> Might work. Are you certain the value are looking for is PCDATA and
>>>> not an attribute
>> value?
>>>>  Mason
>>>>  Sent from my iPhone
>>>>
>>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]> wrote:
>>>>>  Could you post an example of the xml?
>>>>>  Mason
>>>>>  Sent from my iPhone
>>>>>
>>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
>>>>>>  The few times I needed to pull data out of XML, I had a similar  
>>>>>> experience where I couldn't get the xpath correct, and I'd give
>>>>>> up and resort to
>> regex.
>>>>>>   - Bil
>>>>>>
>>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]> wrote:
>>>>>>>  Hi List
>>>>>>>  We are trying to extract data from an Excel spreadsheet, exported as XML.
>>>>>>>  <?LassoScript
>>>>>>>  var('thefileName') = '/uploads/data/sample.xml' ;
>>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
>>>>>>> var('theXML') = xml(var('thefile')) ;
>>>>>>>  Var('Namespaces') = $theXML->(Namespaces); Iterate( $Namespaces,  Var('Temp') );
>>>>>>>    $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
>>>>>>>  var('theRows') =  
>>>>>>> xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
>>>>>>> ;
>>>>>>>  ?>
>>>>>>>  I can see the namespaces as my code returns:
>>>>>>>  = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>> o = urn:schemas-microsoft-com:office:office
>>>>>>> x = urn:schemas-microsoft-com:office:excel
>>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
>>>>>>>   But my rows variable is always empty. I have tried lots of  
>>>>>>> variations with the xpath including " //*:Row", but nothing
>>>>>>> seems  to work. The data I want to extract is in the "ss"
>>>>>>> namespace
>> (urn:schemas-microsoft-com:
>>>>>>> office:spreadsheet).
>>>>>>>  I can see that in xmlstream I can specify namespaces, but how do I  use them within my xpath?
>>>>>>>  Any help appreciated.
>>>>>>>  Jon Harris
>>>>>>>   #############################################################
>>>>>>>  This message is sent to you because you are subscribed to the  mailing list Lasso [hidden email] Official list  archives available at http://www.lassotalk.com To unsubscribe,  E-mail to: <[hidden email]>
>>>>>>> Send administrative queries to   <[hidden email]>
>>>>>>>
>>>>>>  #############################################################
>>>>>>  This message is sent to you because you are subscribed to the  
>>>>>> mailing list Lasso [hidden email] Official list
>>>>>> archives  available at http://www.lassotalk.com To unsubscribe,
>>>>>> E-mail to:  <[hidden email]>
>>>>>> Send administrative queries to  
>>>>>> <[hidden email]>
>>>>>    #############################################################
>>>>>  This message is sent to you because you are subscribed to the  
>>>>> mailing list Lasso [hidden email] Official list
>>>>> archives  available at http://www.lassotalk.com To unsubscribe,
>>>>> E-mail to:  <[hidden email]>
>>>>> Send administrative queries to  
>>>>> <[hidden email]>
>>>>  #############################################################
>>>>  This message is sent to you because you are subscribed to the
>>>> mailing  list Lasso [hidden email] Official list
>>>> archives available  at http://www.lassotalk.com To unsubscribe,
>>>> E-mail to:  <[hidden email]>
>>>> Send administrative queries to  <[hidden email]>
>>>
>>> #############################################################
>>>
>>> This message is sent to you because you are subscribed to  the
>>> mailing list Lasso [hidden email] Official list archives
>>> available at http://www.lassotalk.com To unsubscribe, E-mail to:
>>> <[hidden email]>
>>> Send administrative queries to  <[hidden email]>
>>
>>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to the mailing
>> list Lasso [hidden email] Official list archives available
>> at http://www.lassotalk.com To unsubscribe, E-mail to:
>> <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>>
>> #############################################################
>>
>> This message is sent to you because you are subscribed to the mailing
>> list Lasso [hidden email] Official list archives available
>> at http://www.lassotalk.com To unsubscribe, E-mail to:
>> <[hidden email]>
>> Send administrative queries to  <[hidden email]>
>
> -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> Steve Piercy              Website Builder              Soquel, CA
> <[hidden email]>               <http://www.stevepiercy.com/>
>
>
> #############################################################
>
> This message is sent to you because you are subscribed to the mailing
> list Lasso [hidden email] Official list archives available
> at http://www.lassotalk.com To unsubscribe, E-mail to:
> <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: using namespaces in xpath [LP8.6]

Ke Carlton-3
Hello,

All I do is strip the xlns before converting to XML. This greatly
simplifies things:

      local(xml) = xml( #rawdata->replace('xmlns', 'x') &)

I haven't yet come across a situation where the name space has been
required.

One of the correct ways to do with xpath is via local name:

    local(options) = #xml->extract('//*[local-name() = "WorksheetOptions
"]/child::*')

— but I find that too cumbersome.

Once namespaces are removed from the equation xpath is great to use:

with row in #xml->extract('//Row')
skip 1
let values   = #row->extract('Cell/Data/text()')
let title    = #values->get(1)
let forename = #values->get(2)
let surname  = #values->get(3)
let postcode = #values->get(4)
let email    = #values->get(5)
do {
   // Do something with the locals
}

Ke









On Wed, Jun 7, 2017 at 1:09 AM Jon Harris <[hidden email]> wrote:

> Hi Andreas
>
> Thanks for your post, but I think I have a solution using native Lasso. I
> will keep RedBean in my bookmarks.
>
> Regards
> Jon
>
>
> -----Original Message-----
> From: [hidden email] [mailto:
> [hidden email]] On Behalf Of Andreas Steinmann
> Sent: 05 June 2017 09:53 AM
> To: [hidden email]
> Subject: Re: using namespaces in xpath [LP8.6]
>
> I'm in a situation where I have to extract xml data regularly and update a
> local database updating old and adding new records. Couldn't find a way
> with lasso but came across red bean PHP http://redbeanphp.com/index.php
> which does the trick for me every 30 mins pulling the xml from a distant
> server, creating tables, fields, dependencies when needed and updating the
> data. I'm running it as a cron job from the command line. Than I get a nice
> MySQL database and continue working with lasso.
>
> All the best,
> Andreas
>
>
>
> Andreas Steinmann‌
> -------------------------------
> Egebjerghuse 2
> DK 2750 Ballerup
> Denmark
>
> Quartier Meran
> F 06540 Saorge
> France
> -------------------------------
> [hidden email]
> +45 28117505 <+45%2028%2011%2075%2005>
>
> > On 5 Jun 2017, at 10.34, Steve Piercy - Website Builder <
> [hidden email]> wrote:
> >
> > I've never been able to use XML with 'xmlns' in the data, so a regex
> that replaces it with '', then casting it to an xml type has worked for me.
> >
> > See:
> > http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.ht
> > ml
> >
> > --steve
> >
> >
> > On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:
> >
> >> Hi List
> >>
> >> Thanks to everyone who replied over the weekend.  Here is a sample of
> the data, I'm just showing 2 dummy rows below, as we have a couple of
> hundred of real records in the live system.
> >>
> >> Apart from the namespace issue, Excel does seem to create reasonable
> XML.
> >>
> >> As I mentioned in my post, I just want to be able to read the
> individual rows/cells. I've tried using an online xPath generator, then
> pasting that into my lasso xPath and even that doesn't work.
> >>
> >> I think I might have to use Bil Corry's suggestion of RegEx'ing the
> data.
> >> Regards
> >> Jon Harris
> >>
> >>
> >>
> >> <?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>Jon Harris</Author>
> >> <LastAuthor>Jon Harris</LastAuthor>
> >> <Created>2017-06-01T07:37:47Z</Created>
> >> <Version>16.00</Version>
> >> </DocumentProperties>
> >> <OfficeDocumentSettings
> >> xmlns="urn:schemas-microsoft-com:office:office">
> >> <AllowPNG/>
> >> </OfficeDocumentSettings>
> >> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
> >> <WindowHeight>9855</WindowHeight>
> >> <WindowWidth>27975</WindowWidth>
> >> <WindowTopX>0</WindowTopX>
> >> <WindowTopY>0</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="EBlast user data."> <Table ss:ExpandedColumnCount="5"
> >> ss:ExpandedRowCount="47" x:FullColumns="1"
> >> x:FullRows="1" ss:DefaultRowHeight="15"> <Row> <Cell><Data
> >> ss:Type="String">Title</Data></Cell>
> >> <Cell><Data ss:Type="String">Forename</Data></Cell>
> >> <Cell><Data ss:Type="String">Surname</Data></Cell>
> >> <Cell><Data ss:Type="String">Postcode</Data></Cell>
> >> <Cell><Data ss:Type="String">Email</Data></Cell>
> >> </Row>
> >> <Row>
> >> <Cell><Data ss:Type="String">Mrs</Data></Cell>
> >> <Cell><Data ss:Type="String">Jane</Data></Cell>
> >> <Cell><Data ss:Type="String">Doe</Data></Cell>
> >> <Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell> <Cell><Data
> >> ss:Type="String">[hidden email]</Data></Cell>
> >> </Row>
> >> <Row>
> >> <Cell><Data ss:Type="String">Mr</Data></Cell> <Cell><Data
> >> ss:Type="String">John</Data></Cell>
> >> <Cell><Data ss:Type="String">Doe</Data></Cell>
> >> <Cell><Data ss:Type="String">AB1 2CD</Data></Cell> <Cell><Data
> >> ss:Type="String">[hidden email]</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/>
> >> <ProtectObjects>False</ProtectObjects>
> >> <ProtectScenarios>False</ProtectScenarios>
> >> </WorksheetOptions>
> >> </Worksheet>
> >> </Workbook>
> >>
> >>
> >> -----Original Message-----
> >> From: [hidden email]
> >> [mailto:[hidden email]] On Behalf Of Mason Miller
> >> Sent: 03 June 2017 12:35 AM
> >> To: [hidden email]
> >> Subject: Re: using namespaces in xpath [LP8.6]
> >>
> >> One final thought is that if there is more than one occurrence in the
> document, you need to iterate through those.
> >> Mason
> >>
> >> Sent from my iPhone
> >>
> >> On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
> >>
> >>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
> >>>
> >>>
> >>> Will match on your namespace.
> >>> Sent from my iPhone
> >>>
> >>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]>
> wrote:
> >>>>  //*[name()='Row'][1]/*[name()='Cell'][1]/text()
> >>>> Might work. Are you certain the value are looking for is PCDATA and
> >>>> not an attribute
> >> value?
> >>>>  Mason
> >>>>  Sent from my iPhone
> >>>>
> >>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]>
> wrote:
> >>>>>  Could you post an example of the xml?
> >>>>>  Mason
> >>>>>  Sent from my iPhone
> >>>>>
> >>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
> >>>>>>  The few times I needed to pull data out of XML, I had a similar
> >>>>>> experience where I couldn't get the xpath correct, and I'd give
> >>>>>> up and resort to
> >> regex.
> >>>>>>   - Bil
> >>>>>>
> >>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris <[hidden email]>
> wrote:
> >>>>>>>  Hi List
> >>>>>>>  We are trying to extract data from an Excel spreadsheet, exported
> as XML.
> >>>>>>>  <?LassoScript
> >>>>>>>  var('thefileName') = '/uploads/data/sample.xml' ;
> >>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
> >>>>>>> var('theXML') = xml(var('thefile')) ;
> >>>>>>>  Var('Namespaces') = $theXML->(Namespaces); Iterate( $Namespaces,
> Var('Temp') );
> >>>>>>>    $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
> >>>>>>>  var('theRows') =
> >>>>>>> xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()')
> >>>>>>> ;
> >>>>>>>  ?>
> >>>>>>>  I can see the namespaces as my code returns:
> >>>>>>>  = urn:schemas-microsoft-com:office:spreadsheet
> >>>>>>> o = urn:schemas-microsoft-com:office:office
> >>>>>>> x = urn:schemas-microsoft-com:office:excel
> >>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
> >>>>>>>   But my rows variable is always empty. I have tried lots of
> >>>>>>> variations with the xpath including " //*:Row", but nothing
> >>>>>>> seems  to work. The data I want to extract is in the "ss"
> >>>>>>> namespace
> >> (urn:schemas-microsoft-com:
> >>>>>>> office:spreadsheet).
> >>>>>>>  I can see that in xmlstream I can specify namespaces, but how do
> I  use them within my xpath?
> >>>>>>>  Any help appreciated.
> >>>>>>>  Jon Harris
> >>>>>>>   #############################################################
> >>>>>>>  This message is sent to you because you are subscribed to the
> mailing list Lasso [hidden email] Official list  archives
> available at http://www.lassotalk.com To unsubscribe,  E-mail to: <
> [hidden email]>
> >>>>>>> Send administrative queries to   <
> [hidden email]>
> >>>>>>>
> >>>>>>  #############################################################
> >>>>>>  This message is sent to you because you are subscribed to the
> >>>>>> mailing list Lasso [hidden email] Official list
> >>>>>> archives  available at http://www.lassotalk.com To unsubscribe,
> >>>>>> E-mail to:  <[hidden email]>
> >>>>>> Send administrative queries to
> >>>>>> <[hidden email]>
> >>>>>    #############################################################
> >>>>>  This message is sent to you because you are subscribed to the
> >>>>> mailing list Lasso [hidden email] Official list
> >>>>> archives  available at http://www.lassotalk.com To unsubscribe,
> >>>>> E-mail to:  <[hidden email]>
> >>>>> Send administrative queries to
> >>>>> <[hidden email]>
> >>>>  #############################################################
> >>>>  This message is sent to you because you are subscribed to the
> >>>> mailing  list Lasso [hidden email] Official list
> >>>> archives available  at http://www.lassotalk.com To unsubscribe,
> >>>> E-mail to:  <[hidden email]>
> >>>> Send administrative queries to  <[hidden email]>
> >>>
> >>> #############################################################
> >>>
> >>> This message is sent to you because you are subscribed to  the
> >>> mailing list Lasso [hidden email] Official list archives
> >>> available at http://www.lassotalk.com To unsubscribe, E-mail to:
> >>> <[hidden email]>
> >>> Send administrative queries to  <[hidden email]>
> >>
> >>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to the mailing
> >> list Lasso [hidden email] Official list archives available
> >> at http://www.lassotalk.com To unsubscribe, E-mail to:
> >> <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to the mailing
> >> list Lasso [hidden email] Official list archives available
> >> at http://www.lassotalk.com To unsubscribe, E-mail to:
> >> <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy              Website Builder              Soquel, CA
> > <[hidden email]>               <http://www.stevepiercy.com/>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to the mailing
> > list Lasso [hidden email] Official list archives available
> > at http://www.lassotalk.com To unsubscribe, E-mail to:
> > <[hidden email]>
> > Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email] Official list archives
> available at http://www.lassotalk.com To unsubscribe, E-mail to: <
> [hidden email]>
> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email]
> Official list archives available at http://www.lassotalk.com
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: using namespaces in xpath [LP8.6]

Jon Harris-2
Hi List

Just a quick follow up. Finally, got back to this project.

Yes, thanks - removing the namespaces, ended up as a very simple fix.

var('ModifiedfileData') = String_ReplaceRegExp( var('thefiledata') , -find=' xmlns=".*?"', -replace='') ;

Regards
Jon



-----Original Message-----
From: [hidden email] [mailto:[hidden email]] On Behalf Of Ke Carlton
Sent: 06 June 2017 11:37 PM
To: [hidden email]
Subject: Re: using namespaces in xpath [LP8.6]

Hello,

All I do is strip the xlns before converting to XML. This greatly simplifies things:

      local(xml) = xml( #rawdata->replace('xmlns', 'x') &)

I haven't yet come across a situation where the name space has been required.

One of the correct ways to do with xpath is via local name:

    local(options) = #xml->extract('//*[local-name() = "WorksheetOptions
"]/child::*')

— but I find that too cumbersome.

Once namespaces are removed from the equation xpath is great to use:

with row in #xml->extract('//Row')
skip 1
let values   = #row->extract('Cell/Data/text()')
let title    = #values->get(1)
let forename = #values->get(2)
let surname  = #values->get(3)
let postcode = #values->get(4)
let email    = #values->get(5)
do {
   // Do something with the locals
}

Ke









On Wed, Jun 7, 2017 at 1:09 AM Jon Harris <[hidden email]> wrote:

> Hi Andreas
>
> Thanks for your post, but I think I have a solution using native
> Lasso. I will keep RedBean in my bookmarks.
>
> Regards
> Jon
>
>
> -----Original Message-----
> From: [hidden email] [mailto:
> [hidden email]] On Behalf Of Andreas Steinmann
> Sent: 05 June 2017 09:53 AM
> To: [hidden email]
> Subject: Re: using namespaces in xpath [LP8.6]
>
> I'm in a situation where I have to extract xml data regularly and
> update a local database updating old and adding new records. Couldn't
> find a way with lasso but came across red bean PHP
> http://redbeanphp.com/index.php which does the trick for me every 30
> mins pulling the xml from a distant server, creating tables, fields,
> dependencies when needed and updating the data. I'm running it as a
> cron job from the command line. Than I get a nice MySQL database and continue working with lasso.
>
> All the best,
> Andreas
>
>
>
> Andreas Steinmann‌
> -------------------------------
> Egebjerghuse 2
> DK 2750 Ballerup
> Denmark
>
> Quartier Meran
> F 06540 Saorge
> France
> -------------------------------
> [hidden email]
> +45 28117505 <+45%2028%2011%2075%2005>
>
> > On 5 Jun 2017, at 10.34, Steve Piercy - Website Builder <
> [hidden email]> wrote:
> >
> > I've never been able to use XML with 'xmlns' in the data, so a regex
> that replaces it with '', then casting it to an xml type has worked for me.
> >
> > See:
> > http://lasso.2283332.n4.nabble.com/Xpath-problems-tp3132939p3132940.
> > ht
> > ml
> >
> > --steve
> >
> >
> > On 6/5/17 at 8:04 AM, [hidden email] (Jon Harris) pronounced:
> >
> >> Hi List
> >>
> >> Thanks to everyone who replied over the weekend.  Here is a sample
> >> of
> the data, I'm just showing 2 dummy rows below, as we have a couple of
> hundred of real records in the live system.
> >>
> >> Apart from the namespace issue, Excel does seem to create
> >> reasonable
> XML.
> >>
> >> As I mentioned in my post, I just want to be able to read the
> individual rows/cells. I've tried using an online xPath generator,
> then pasting that into my lasso xPath and even that doesn't work.
> >>
> >> I think I might have to use Bil Corry's suggestion of RegEx'ing the
> data.
> >> Regards
> >> Jon Harris
> >>
> >>
> >>
> >> <?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>Jon Harris</Author>
> >> <LastAuthor>Jon Harris</LastAuthor>
> >> <Created>2017-06-01T07:37:47Z</Created>
> >> <Version>16.00</Version>
> >> </DocumentProperties>
> >> <OfficeDocumentSettings
> >> xmlns="urn:schemas-microsoft-com:office:office">
> >> <AllowPNG/>
> >> </OfficeDocumentSettings>
> >> <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
> >> <WindowHeight>9855</WindowHeight>
> >> <WindowWidth>27975</WindowWidth>
> >> <WindowTopX>0</WindowTopX>
> >> <WindowTopY>0</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="EBlast user data."> <Table ss:ExpandedColumnCount="5"
> >> ss:ExpandedRowCount="47" x:FullColumns="1"
> >> x:FullRows="1" ss:DefaultRowHeight="15"> <Row> <Cell><Data
> >> ss:Type="String">Title</Data></Cell>
> >> <Cell><Data ss:Type="String">Forename</Data></Cell>
> >> <Cell><Data ss:Type="String">Surname</Data></Cell>
> >> <Cell><Data ss:Type="String">Postcode</Data></Cell>
> >> <Cell><Data ss:Type="String">Email</Data></Cell>
> >> </Row>
> >> <Row>
> >> <Cell><Data ss:Type="String">Mrs</Data></Cell>
> >> <Cell><Data ss:Type="String">Jane</Data></Cell>
> >> <Cell><Data ss:Type="String">Doe</Data></Cell>
> >> <Cell><Data ss:Type="String">ZZ1 1XW</Data></Cell> <Cell><Data
> >> ss:Type="String">[hidden email]</Data></Cell>
> >> </Row>
> >> <Row>
> >> <Cell><Data ss:Type="String">Mr</Data></Cell> <Cell><Data
> >> ss:Type="String">John</Data></Cell>
> >> <Cell><Data ss:Type="String">Doe</Data></Cell>
> >> <Cell><Data ss:Type="String">AB1 2CD</Data></Cell> <Cell><Data
> >> ss:Type="String">[hidden email]</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/>
> >> <ProtectObjects>False</ProtectObjects>
> >> <ProtectScenarios>False</ProtectScenarios>
> >> </WorksheetOptions>
> >> </Worksheet>
> >> </Workbook>
> >>
> >>
> >> -----Original Message-----
> >> From: [hidden email]
> >> [mailto:[hidden email]] On Behalf Of Mason
> >> Miller
> >> Sent: 03 June 2017 12:35 AM
> >> To: [hidden email]
> >> Subject: Re: using namespaces in xpath [LP8.6]
> >>
> >> One final thought is that if there is more than one occurrence in
> >> the
> document, you need to iterate through those.
> >> Mason
> >>
> >> Sent from my iPhone
> >>
> >> On Jun 2, 2017, at 7:19 PM, Mason Miller <[hidden email]> wrote:
> >>
> >>>> //*[qname()='ss:Row'][1]/*[qname()='ss:Cell'][1]/text()
> >>>
> >>>
> >>> Will match on your namespace.
> >>> Sent from my iPhone
> >>>
> >>>> On Jun 2, 2017, at 7:14 PM, Mason Miller <[hidden email]>
> wrote:
> >>>>  //*[name()='Row'][1]/*[name()='Cell'][1]/text()
> >>>> Might work. Are you certain the value are looking for is PCDATA
> >>>> and not an attribute
> >> value?
> >>>>  Mason
> >>>>  Sent from my iPhone
> >>>>
> >>>>> On Jun 2, 2017, at 7:08 PM, Mason Miller <[hidden email]>
> wrote:
> >>>>>  Could you post an example of the xml?
> >>>>>  Mason
> >>>>>  Sent from my iPhone
> >>>>>
> >>>>>> On Jun 2, 2017, at 1:23 PM, Bil Corry <[hidden email]> wrote:
> >>>>>>  The few times I needed to pull data out of XML, I had a
> >>>>>> similar experience where I couldn't get the xpath correct, and
> >>>>>> I'd give up and resort to
> >> regex.
> >>>>>>   - Bil
> >>>>>>
> >>>>>>> On Fri, Jun 2, 2017 at 3:21 AM, Jon Harris
> >>>>>>> <[hidden email]>
> wrote:
> >>>>>>>  Hi List
> >>>>>>>  We are trying to extract data from an Excel spreadsheet,
> >>>>>>> exported
> as XML.
> >>>>>>>  <?LassoScript
> >>>>>>>  var('thefileName') = '/uploads/data/sample.xml' ;
> >>>>>>> var('thefile') = file_read( var('thefileName')  ) ;
> >>>>>>> var('theXML') = xml(var('thefile')) ;
> >>>>>>>  Var('Namespaces') = $theXML->(Namespaces); Iterate(
> >>>>>>> $Namespaces,
> Var('Temp') );
> >>>>>>>    $Temp->First + ' = ' + $Temp->Second "<br />" ; /Iterate;
> >>>>>>>  var('theRows') =
> >>>>>>> xml_extract(-xml=var('theXML'),-Xpath='//Row[1]/Cell[1]/text()
> >>>>>>> ')
> >>>>>>> ;
> >>>>>>>  ?>
> >>>>>>>  I can see the namespaces as my code returns:
> >>>>>>>  = urn:schemas-microsoft-com:office:spreadsheet
> >>>>>>> o = urn:schemas-microsoft-com:office:office
> >>>>>>> x = urn:schemas-microsoft-com:office:excel
> >>>>>>> ss = urn:schemas-microsoft-com:office:spreadsheet
> >>>>>>>   But my rows variable is always empty. I have tried lots of
> >>>>>>> variations with the xpath including " //*:Row", but nothing
> >>>>>>> seems  to work. The data I want to extract is in the "ss"
> >>>>>>> namespace
> >> (urn:schemas-microsoft-com:
> >>>>>>> office:spreadsheet).
> >>>>>>>  I can see that in xmlstream I can specify namespaces, but how
> >>>>>>> do
> I  use them within my xpath?
> >>>>>>>  Any help appreciated.
> >>>>>>>  Jon Harris
> >>>>>>>  
> >>>>>>> #############################################################
> >>>>>>>  This message is sent to you because you are subscribed to the
> mailing list Lasso [hidden email] Official list  archives
> available at http://www.lassotalk.com To unsubscribe,  E-mail to: <
> [hidden email]>
> >>>>>>> Send administrative queries to   <
> [hidden email]>
> >>>>>>>
> >>>>>>  #############################################################
> >>>>>>  This message is sent to you because you are subscribed to the
> >>>>>> mailing list Lasso [hidden email] Official list
> >>>>>> archives  available at http://www.lassotalk.com To unsubscribe,
> >>>>>> E-mail to:  <[hidden email]>
> >>>>>> Send administrative queries to
> >>>>>> <[hidden email]>
> >>>>>    #############################################################
> >>>>>  This message is sent to you because you are subscribed to the
> >>>>> mailing list Lasso [hidden email] Official list
> >>>>> archives  available at http://www.lassotalk.com To unsubscribe,
> >>>>> E-mail to:  <[hidden email]>
> >>>>> Send administrative queries to
> >>>>> <[hidden email]>
> >>>>  #############################################################
> >>>>  This message is sent to you because you are subscribed to the
> >>>> mailing  list Lasso [hidden email] Official list
> >>>> archives available  at http://www.lassotalk.com To unsubscribe,
> >>>> E-mail to:  <[hidden email]>
> >>>> Send administrative queries to  
> >>>> <[hidden email]>
> >>>
> >>> #############################################################
> >>>
> >>> This message is sent to you because you are subscribed to  the
> >>> mailing list Lasso [hidden email] Official list
> >>> archives available at http://www.lassotalk.com To unsubscribe, E-mail to:
> >>> <[hidden email]>
> >>> Send administrative queries to  
> >>> <[hidden email]>
> >>
> >>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to the
> >> mailing list Lasso [hidden email] Official list archives
> >> available at http://www.lassotalk.com To unsubscribe, E-mail to:
> >> <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >>
> >> #############################################################
> >>
> >> This message is sent to you because you are subscribed to the
> >> mailing list Lasso [hidden email] Official list archives
> >> available at http://www.lassotalk.com To unsubscribe, E-mail to:
> >> <[hidden email]>
> >> Send administrative queries to  <[hidden email]>
> >
> > -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
> > Steve Piercy              Website Builder              Soquel, CA
> > <[hidden email]>               <http://www.stevepiercy.com/>
> >
> >
> > #############################################################
> >
> > This message is sent to you because you are subscribed to the
> > mailing list Lasso [hidden email] Official list archives
> > available at http://www.lassotalk.com To unsubscribe, E-mail to:
> > <[hidden email]>
> > Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email] Official list
> archives available at http://www.lassotalk.com To unsubscribe, E-mail
> to: < [hidden email]>
> Send administrative queries to  <[hidden email]>
>
> #############################################################
>
> This message is sent to you because you are subscribed to
>   the mailing list Lasso [hidden email] Official list
> archives available at http://www.lassotalk.com To unsubscribe, E-mail
> to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email] Official list archives available at http://www.lassotalk.com To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>

#############################################################

This message is sent to you because you are subscribed to
  the mailing list Lasso [hidden email]
Official list archives available at http://www.lassotalk.com
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>