[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 288
  • Last Modified:

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.
0
Xtianu
Asked:
Xtianu
  • 6
  • 5
1 Solution
 
Scott BennettCommented:
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
 
Scott BennettCommented:
<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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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 BennettCommented:
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 BennettCommented:
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 BennettCommented:
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 BennettCommented:
CF8 is more forgiving with xml
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now