How to load and parse XML from cursor field? Suggested XML parser? 2

[Manuch]

Hello,
sorry to bother, but I'm in a bit of a rush and I always forget everything about FoxPro, I did a bit of research but didn't find an answer on the net.

I have a table with an XML field, I load it in a cursor with SqlExec

Now I would like to feed this XML contained in a field of the row of this cursor to a XMLAdapter instance or any XML parser, really I don't remember how I used to parse XMLs in FoxPro.
Can you please tell how can I do this and what you would use to parse the XML?
The structure of the XML is always

[pre]<father>

​

<child attr="1"></child>

​

<child attr="2"></child>

​

<child attr="3"></child>
</father>[/pre]

I just need to check if the rows of the cursor, in their XMLs, have a child with a specific value for the attribute "attr"

Thank you, again sorry to bother

 

[Mike Lewis]

Would something like this work for you:
[tt]
lnRecs = XMLTOCURSOR(_xmlAR.attri, "csrResult")[/tt]

There are quite a few variations of XMLTOCURSOR(), so check its Help topic, especially the Flags parameter.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Manuch]

Thank you very much Mike, that's exactly what I was looking for, couldn't ask for better, thanks, have a great day.

 

[Chris Miller]

Well you get a cursor with a field called attr in that case. So you get the data, good enough, but VFP xmltocursor is not a magic tool, it has its limits.

You can make use of SQL Server XML functions.

Local lcSQL
Text To lcSQL noshow
DECLARE @myxml XML
Select @myxml = xmlfield from test where ...the condition from your screenshot...
Select child.value('@attr[1]','int') as attr from @myxml.nodes('/father/child') AS T(child)
Endtext
SQLExec(conn,lcSQL,'result')

Yes, this surely looks less general but at times it's your best bet to process XML with the T-SQL xml type methods and things like XQuery. To break this code down (in it's major parts), the first step is to get one XML value into a SQL Server variable, then the nodes method is used to extract all 'child' nodes into a table of separate xml nodes called T with an xml field 'child'. And as last step the value method is used on each child xml node, to extract the attribute value via @attr[1] as an integer. So here you're using XQuery (nodes) to get at separate nodes and then the scalpel tool value to get at the attribute value. This will need to be adopted to whatever the XML is, but once you go into the details of XQuery you can get at what you need exactly and precisely.

Another such scalpel tool in the string functions belt of VFP is StrExtract(), look at it in the help, it's not specialized on XML but easily adapts to any of the SGML ways of opening/closing tags to use as delimiters of string extraction.

And much more generally, if you start including XML into your data, also get familiar with what SQL Server offers and not only think about getting the XML out for doing everything else in VFP. Because it's an SQL Server data type and it's stored in XML formats SQL Server can parse, at least when a developer isn't just as lazy as storing the XML string.

Well, and when it comes to VFP capabilities, then even before VFP7 introduced some XML functions Rick Strahl already had a good XML library called wwXML. And it's included in his free West Winds Internet & Client Tools. It also has more web related functionalities like the smaller brother JSON. And about http, even about email. There are more developers offering VFP github repositories on the topic of XML or JSON in the forum, just look around and you'll find them mentioned in many threads here.

Chriss