Parsing XML page into database

I've got a XML page at a website that I need to fetch and populate into a MSSQL database. The XML contains the following:

<api version="1">
    <currentTime>2007-06-21 17:49:22</currentTime>
    <result>
        <rowset name="entries" key="refID">
            <row date="2007-06-15 14:13:00" refID="59149" refTypeID="54" ownerName1="test" ownerID1="150337897" ownerName2="test2" ownerID2="1000132" argName1="" argID1="0" amount="-7.93" balance="190210393.87" reason=""/>
        </rowset>
    </result>
    <cachedUntil>2007-06-21 17:49:32</cachedUntil>
</api>

The row tag can exist in singles, or thousands. Everything needs to be copied over to the DB using the refID as the key. Up to 1000 rows can be listed.

Ideas - since I'm completely lost at this, never touched anything simlar.
XtianuAsked:
Who is Participating?
 
Scott BennettManager TechnologyCommented:
<cfhttp url="url to xml file here" method="GET"></cfhttp>

<cfset MyXMLDoc = xmlParse(cfhttp.filecontent)>

<cfset xmlRows = xmlSearch(MyXMLDoc,"/api/result/rowset/row")>

<cfoutput>
<cfloop from="1" to="#arraylen(xmlRows)#" index="i">
      <cfset RowXML = xmlparse(xmlRows[i])>
      <b>date:</b> #RowXML.row.XmlAttributes.date#<br>
      <b>refID:</b> #RowXML.row.XmlAttributes.refID#<br>
      <b>refTypeID:</b> #RowXML.row.XmlAttributes.refTypeID#<br>
      <b>ownerName1:</b> #RowXML.row.XmlAttributes.ownerName1#<br>
      <b>ownerID1:</b> #RowXML.row.XmlAttributes.ownerID1#<br>
      <b>ownerName2:</b> #RowXML.row.XmlAttributes.ownerName2#<br>
      <b>ownerID2:</b> #RowXML.row.XmlAttributes.ownerID2#<br>
      <b>argName1:</b> #RowXML.row.XmlAttributes.argName1#<br>
      <b>argID1:</b> #RowXML.row.XmlAttributes.argID1#<br>
      <b>amount:</b> #RowXML.row.XmlAttributes.amount#<br>
      <b>balance:</b> #RowXML.row.XmlAttributes.balance#<br>
      <b>reason:</b> #RowXML.row.XmlAttributes.reason#<br><br><br>
</cfloop>
</cfoutput>
0
 
Scott BennettManager TechnologyCommented:
here is how you would parse out your xml:

<cfsavecontent variable="xmlfile"><?xml version="1.0"?>
<api version="1">
    <currentTime>2007-06-21 17:49:22</currentTime>
    <result>
        <rowset name="entries" key="refID">
            <row date="2007-06-15 14:13:00" refID="59149" refTypeID="54" ownerName1="test1" ownerID1="150337897" ownerName2="test2" ownerID2="1000131" argName1="" argID1="0" amount="-7.93" balance="190210393.87" reason=""/>
                  <row date="2007-06-16 13:14:00" refID="59149" refTypeID="54" ownerName1="test3" ownerID1="150337897" ownerName2="test4" ownerID2="1000132" argName1="" argID1="0" amount="-7.93" balance="190210393.87" reason=""/>
        </rowset>
    </result>
    <cachedUntil>2007-06-21 17:49:32</cachedUntil>
</api></cfsavecontent>

<cfset MyXMLDoc = xmlParse(XMLFile)>

<cfset xmlRows = xmlSearch(MyXMLDoc,"/api/result/rowset/row")>

<cfoutput>
<cfloop from="1" to="#arraylen(xmlRows)#" index="i">
      <cfset RowXML = xmlparse(xmlRows[i])>
      <b>date:</b> #RowXML.row.XmlAttributes.date#<br>
      <b>refID:</b> #RowXML.row.XmlAttributes.refID#<br>
      <b>refTypeID:</b> #RowXML.row.XmlAttributes.refTypeID#<br>
      <b>ownerName1:</b> #RowXML.row.XmlAttributes.ownerName1#<br>
      <b>ownerID1:</b> #RowXML.row.XmlAttributes.ownerID1#<br>
      <b>ownerName2:</b> #RowXML.row.XmlAttributes.ownerName2#<br>
      <b>ownerID2:</b> #RowXML.row.XmlAttributes.ownerID2#<br>
      <b>argName1:</b> #RowXML.row.XmlAttributes.argName1#<br>
      <b>argID1:</b> #RowXML.row.XmlAttributes.argID1#<br>
      <b>amount:</b> #RowXML.row.XmlAttributes.amount#<br>
      <b>balance:</b> #RowXML.row.XmlAttributes.balance#<br>
      <b>reason:</b> #RowXML.row.XmlAttributes.reason#<br><br><br>
</cfloop>
</cfoutput>

Then just use thos variables in your query
0
 
XtianuAuthor Commented:
The XML file is located on another webserver (not mine) so I need the page to somehow fetch the XML page from that server. I've been toying with cfhhtp but I'm not sure how it 'stores' it while parsing the rest of the CFM page so I can't get it working like I want.

So, I assume the cfsavecontent needs to be switched with something else, ones that's done - I guess it should all be taken care of.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
XtianuAuthor Commented:
Did a copy & paste of your code, just to start tinkering with it and got the following error:

An error occured while Parsing an XML document.  
The processing instruction target matching "[xX][mM][lL]" is not allowed.  
 
The error occurred in H:\wwwroot\iac\xml\fetch.cfm: line 18
 
16 : </api></cfsavecontent>
17 :
18 : <cfset MyXMLDoc = xmlParse(XMLFile)>
19 :
20 : <cfset xmlRows = xmlSearch(MyXMLDoc,"/api/result/rowset/row")>
 
0
 
Scott BennettManager TechnologyCommented:
make sure your xml file starts with at <?xml version="1.0"?> tag
0
 
XtianuAuthor Commented:
You're fast on comments! :) Can I borrow your brain? It seems handy


An error occured while Parsing an XML document.  
Content is not allowed in prolog.  
 
The error occurred in H:\wwwroot\xml\fetch.cfm: line 7
 
5 : <cfhttp url="http:// remove server url /xml/test.xml" method="GET"></cfhttp>
6 :
7 : <cfset MyXMLDoc = xmlParse(cfhttp.filecontent)>
8 :
9 : <cfset xmlRows = xmlSearch(MyXMLDoc,"/api/result/rowset/row")>

 
0
 
Scott BennettManager TechnologyCommented:
make sure there are not spaces or anything before the xml document declaration (<?xml version="1.0"?> ) the xml document declaration shoulb the the veryfirst character xmlparse sees in the string.

you can also use the trim() function to remove whitespace that might come before it ifyou think your xml files might have it.

 <cfset MyXMLDoc = xmlParse(trim(cfhttp.filecontent))>
0
 
Scott BennettManager TechnologyCommented:
I'd let you have my brain, except i'm a little attached to it..... but for the right price....
0
 
XtianuAuthor Commented:
Still the same error message.

XML file:
<?xml version="1.0"?>
<api version="1">
    <currentTime>2007-06-21 17:49:22</currentTime>
    <result>
        <rowset name="entries" key="refID">
            <row date="2007-06-15 14:13:00" refID="59149" refTypeID="54" ownerName1="test1" ownerID1="150337897" ownerName2="test2" ownerID2="1000131" argName1="" argID1="0" amount="-7.93" balance="190210393.87" reason=""/>
                  <row date="2007-06-16 13:14:00" refID="59149" refTypeID="54" ownerName1="test3" ownerID1="150337897" ownerName2="test4" ownerID2="1000132" argName1="" argID1="0" amount="-7.93" balance="190210393.87" reason=""/>
        </rowset>
    </result>
    <cachedUntil>2007-06-21 17:49:32</cachedUntil>
</api>
-- end of file

code beeing used:
<cfhttp url="http://removed/xml/test.xml" method="GET"></cfhttp>

<cfset MyXMLDoc = xmlParse(trim(cfhttp.filecontent))>

<cfset xmlRows = xmlSearch(MyXMLDoc,"/api/result/rowset/row")>

<cfoutput>
<cfloop from="1" to="#arraylen(xmlRows)#" index="i">
      <cfset RowXML = xmlparse(xmlRows[i])>
      <b>date:</b> #RowXML.row.XmlAttributes.date#<br>
      <b>refID:</b> #RowXML.row.XmlAttributes.refID#<br>
      <b>refTypeID:</b> #RowXML.row.XmlAttributes.refTypeID#<br>
      <b>ownerName1:</b> #RowXML.row.XmlAttributes.ownerName1#<br>
      <b>ownerID1:</b> #RowXML.row.XmlAttributes.ownerID1#<br>
      <b>ownerName2:</b> #RowXML.row.XmlAttributes.ownerName2#<br>
      <b>ownerID2:</b> #RowXML.row.XmlAttributes.ownerID2#<br>
      <b>argName1:</b> #RowXML.row.XmlAttributes.argName1#<br>
      <b>argID1:</b> #RowXML.row.XmlAttributes.argID1#<br>
      <b>amount:</b> #RowXML.row.XmlAttributes.amount#<br>
      <b>balance:</b> #RowXML.row.XmlAttributes.balance#<br>
      <b>reason:</b> #RowXML.row.XmlAttributes.reason#<br><br><br>
</cfloop>
</cfoutput>

Wonder what the going price for brains is these days ..
0
 
XtianuAuthor Commented:
Hrm.

Copied it over to another dev server and it worked. Difference beeing one runing CFM7.0.2, the other runing CFM8. So it's all working fine now - awesome, thanks! :)
0
 
Scott BennettManager TechnologyCommented:
CF8 is more forgiving with xml
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.