XML Newbie...parsing XML
Hi all,
I'm fairly new to XML, I understand what it is and how to use stylesheets etc. However, I'm trying to do the following and need some tips on how to go about it..
I am using a report tool to pull data back from a central database. The results come back in XML and are displayed correctly using a stylesheet.
What I need to do is parse the returned XML and search for certain records so I can pull out subsets of data. I also then need to make sure I keep all the header information (such as column headers etc.) and footers intact.
So, for example I have my XML return all records for regions 1,2,3,4. I need to say, ok, I just want to see region 3's data and therefore pull out the header, get the region 3 data, then pull out the footer to make one single report.
I can't filter it at the table level (where you would normally do it) because the XML is cached every day and this is our only source of data.
Any ideas?? Thanks!
[1009 byte] By [
alougher] at [2007-11-18 16:38:01]
# 1 Re: XML Newbie...parsing XML
Sounds like you could just add some more stylesheets.
Based on what little info you have posted. I can't really say anything else. You will have to be much much more specific if you want more help with this.
khp at 2007-11-10 3:28:20 >
# 2 Re: XML Newbie...parsing XML
I didn't want to post too much detail, in fact, I couldn't as I don't have the source code in front of me. However...
Basically, this is the situation:
Report tool queries database returns one HUGE string containing all the XML.
I then use MSXML to parse and convert this to HTML using a stylesheet out-of-the-box.
XML displays correctly, in a nice table with column headers, something like this:
Region Store Revenue
1 55 $1000
1 56 $2000
2 10 $500
3 90 $100
So I know that in the XML is the column headings, report title etc. So what if I then wanted to look in the XML code (or the returned string before it goes through MSXML) and say ok, I only want Region 2's data. Originally, I just thought I could do an instring to get to region 2's record and just pull out the xml code. But alas, then I lose all the header/footer information.
I'll try and post the actual code (have it at work) which might help!
# 3 Re: XML Newbie...parsing XML
I found this code on the net, is perhaps the selectnodes method/property the answer? So I would do something like selectnodes("/region") ?
Option Explicit
Public XMLdoc As MSXML2.DOMDocument40
Private Sub cmdRead_Click()
Dim oxmlOrders As IXMLDOMNodeList
Dim oxmlItem As IXMLDOMNode
Dim oxmlInvoice As IXMLDOMNode
Dim lngOrderNdx As Long
Dim lngInvoiceNdx As Long
On Error GoTo ErrorRoutine
Set XMLdoc = New DOMDocument40
XMLdoc.async = False
XMLdoc.validateOnParse = False
XMLdoc.preserveWhiteSpace = True
XMLdoc.Load "C:\VBF\orderlink_9\orderlink_9.xml"
' Get all the orders. Your example file has only one but I assume there could
' be more
Set oxmlOrders = XMLdoc.documentElement.selectNodes("/order")
' length is the number of orders found
For lngOrderNdx = 0 To oxmlOrders.length - 1
' the ./ notation means search the xml "tree" from where you left off
Set oxmlItem = oxmlOrders(lngOrderNdx).selectSingleNode("./username")
Debug.Print "username: " & oxmlItem.Text
' XML is case sensitive so Orderid would NOT work here
Set oxmlItem = oxmlOrders(lngOrderNdx).selectSingleNode("./orderid")
Debug.Print "orderid: " & oxmlItem.Text
Set oxmlItem = oxmlOrders(lngOrderNdx).selectSingleNode("./contid")
Debug.Print "contid: " & oxmlItem.Text
Set oxmlItem = oxmlOrders(lngOrderNdx).selectSingleNode("./contname")
Debug.Print "contname: " & oxmlItem.Text
' Look at the children of the order to find the invoices.
For lngInvoiceNdx = 0 To oxmlOrders(lngOrderNdx).childNodes.length - 1
If oxmlOrders(lngOrderNdx).childNodes(lngInvoiceNdx).nodeName = "adrinvoice" Then
Debug.Print "adrinvoice: " & oxmlOrders(lngOrderNdx).childNodes(lngInvoiceNdx).Text
End If
Next
Set oxmlItem = oxmlOrders(lngOrderNdx).selectSingleNode("./datereg")
Debug.Print "datereg: " & oxmlItem.Text
Next
Set oxmlOrders = Nothing
Set oxmlItem = Nothing
Set oxmlInvoice = Nothing
Exit Sub
ErrorRoutine:
MsgBox "Error " & Err.Number & ": " & Err.Description
End Sub
# 4 Re: XML Newbie...parsing XML
Well I'am not really a VB kind of guy (I simply don't understand why anyone would ever use that). But I would think that the selectnodes method selects nodes by name (the argument doesn't appear to be xpath expression, which would have made more sense, but of course this is VB, so ...), I assume that selectNodes("/region") would select all the region nodes in the document. If I understand you correctly you want a specific region element, which I might use an xpath expression like this, to select region 2
//region[number(text())=2]
assuming that your xml code contains something like <region>2</region>
khp at 2007-11-10 3:31:22 >
# 5 Re: XML Newbie...parsing XML
Let me check the code and reply tommorrow, I don't think it's as simple as <Regio>2</region> since the report program has put some long id's in there... let me check it and get back to you - thanks so far!
# 6 Re: XML Newbie...parsing XML
OK, here is the code I need to parse. Basically, from what I understand, I may be able to build this into the XSL right?
<fv sm="2" class="css365AA9ADE4FED3CF81096089A5F1FF8ED">10</fv>
THis is the XSL Code (or some of it):
<xsl:template match="fv">
<xsl:choose>
<xsl:when test="../@mh">
<xsl:eval no-entities="1">NBSP</xsl:eval>
</xsl:when>
<xsl:otherwise><xsl:choose>
<xsl:when test=".[@sm $eq$ '7']">
<xsl:eval no-entities="1">this.text</xsl:eva>
</xsl:when>
<xsl:when test=".[@sm $eq$ '4']"
etc. etc.
Basically, it looks like the XSL finds the tag "fv" (form value) and puts this data as the rows of the report. What I need to do is to filter somehow on the actual value which in this case is "10".
Any thoughts?
Thanks!
# 7 Re: XML Newbie...parsing XML
from what I understand, I may be able to build this into the XSL right?
Yes, that should be relativly easy.
But the selection has to be made at a higher level in the stylesheet. Because it deals with much more than just the fv element.
I can't really say anything else, without knowing the full context.
khp at 2007-11-10 3:34:34 >
# 8 Re: XML Newbie...parsing XML
OK, I've attached the full file.
I downloaded an XML viewer and can see a structure unlike other XML examples I have seen.
If you navigate down through:
//mi/rit/vw/gr/ly/rt/t
You will see an element (or node not sure) called "Store Region".
Then, if you navigate down through:
//mi/rit/vw/gr/rh/rw/h/fv
You will see a value "10". This is Store region 10.
In this example XML there are two Store Regions. Store Region 10 and Store Region 25. I need to filter out (either through code or through the XSL) all the records for Store Region 10.
Oh, I have also attached the XSL so you can see how the report looks on screen.
Also, I found the conversion table for the node descrptions, they are:
<rit> = report_instance
<rd> = report definition
<ord> = original report definition
<vw> = view
<gr> = grid
<ly> = layout
<rt> = row titles, <cts> = column titles, <pts> = page titles
<t> = title
<rh> = row header, <rw> = row
<h> = header
<fv> = form value
<mv> = metric value
<sv> = subtotal value
<chs> = column headers, <ch> = column header
<phs> = page headers, <ph> = page header
<dp> = drill path, <da> = drill action
<f> = filter
<tm> = template
<rsl> = resolution
<gpp> = graph properties, <gdp> = grid properties
<vws> = view setting
Any help really appreciated!
# 9 Re: XML Newbie...parsing XML
Jesus, you xsl file is ugly, in fact it's not even a proper xsl file, but rather an msxsl file. M$ intentionally made their version slightly different from standard xsl, in an effort to lock in their customer.
The interresting part of the stylesheet is the template for the 'rh' element. You could change the line
<xsl:for-each match="rw">
to
<xsl:for-each match="rw[h[number(@rfd)=3]/fv[number(@sm)=2 and number(text()=10)]]">
That should select only the row that contains Region 10 (I'am not sure if this works in msxsl, I suspect it might not, but I only write standard code).
But I'am not quite sure if this is enough, the xml file contains 75 'rw' elements, rw 1 contains the region 10 marker, while rw 42 contains the Region 25 marker. Are any of the other rows needed for anything ? (I'am sorry but I won't bother working this out myself)
khp at 2007-11-10 3:36:30 >
