XML file Query

[Gear_Head]

Hi,

I have access to this XML VIN Decoder, you put in a car VIN and it gives you the car info.

I have never worked with XML before i need to be able to query this like i would a regular Database and get in info into a form field.

I have done a few tutorials but most of them get the file from c:\COLDFUSION...... not in HTTP:// format plus they all use Dump to show the info i really need each field to be put in like:

<cfquery the XML file>

</cfquery>

<cfoutput>

<form name="form1" method="post" action="">

    <input name="Make" type="text" id="Make" value="#make#">
    <input name="Model" type="text" id="Model" value="#model#">

    <input name="Trim Level" type="text" id="Trim Level" value="#Trim Level#">

etch...

  </form>

</cfoutput>

Here is the XML file from the HTTP

  <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <VINQ Version="1.0.0" Report_Type="EXTENDED" Date="10/22/2009">
- <VIN Number="KMHDM55DX5*******" Status="SUCCESS">
- <Vehicle VINquery_Vehicle_ID="29490" Model_Year="2005" Make="Hyundai" Model="Elantra" Trim_Level="GT 5-Door">
  <Item Key="VINquery_Vehicle_ID" Value="29490" Unit="" />
  <Item Key="Model Year" Value="2005" Unit="" />
  <Item Key="Make" Value="Hyundai" Unit="" />
  <Item Key="Model" Value="Elantra" Unit="" />
  <Item Key="Trim Level" Value="GT 5-Door" Unit="" />
  </Vehicle>
  </VIN>
  </VINQ>

Thanks for your help.

[ilssac]

Gear_Head wrote:

Hi,

I have done a few tutorials but most of them get the file from c:\COLDFUSION...... not in HTTP:// format

The easy answer here is that these are the same.  When you read an XML file from someplace like 'c:\COLDFUSION' you end up with a string in a variable that you run through the xmlParse() function to turn it into an XML data structure.

When you receive XML from a HTTP request, you have a string in a variable that you run through the xmlParse() function to turn it into an XML data structure.

plus they all use Dump to show the info i really need each field to be put in like:

This is just a fast and easy way to see the XML data structure you have created.

To use that data structure you reference it like any other data structure.  The most direct way would be with array notation.

<cfoutput>  <!--- note no query parameter we are not utilizing a record set short cut here --->
#xmlStructVar.aNode.bNode.cNode#
OR
#xmlStructVar["aNode"]["bNode"]["cNode"]#
<cfoutput>

There are many ways to access this data, there are childNode Arrays, and xmlText nodes to understand.  You can also use ColdFusion functions like xmlSearch() and xmlTransform() to manipulate the xml data into other data forms.  The documentation and Internet is full of information about all these options.

[Gear_Head]

I get an error message what m i doing wrong ?

<!--- URL for VIN web service ---> <cfset vinqueryURL = "http://www.query to MXL"> <!--- Retrieve the response ---> <cfhttp url="#vinqueryURL#" charset="utf-8"> <!--- Translate the XML response into a CF object. ---> <cfset xmlResult = xmlParse(cfhttp.fileContent)> <!--- Parse returned XML data ---> <cfoutput>

#xmlResult#

</cfoutput

[ilssac]

What error message did you get?

What did you actually do?

Your message may have gotten truncated, it looks incomplete.

[Gear_Head]

This is what i had that wasnt working.

<cfset vinqueryURL = "http://www.Thedomain.com/ws.aspx?accessCode=Myaccesscode&vin=KMHD**********&reportType=2">

<cfhttp url="#vinqueryURL#" charset="utf-8">

<cfif not isXML(cfhttp.fileContent)>
<cfthrow message="VINquery web service did not return valid XML.">
</cfif>

<cfset xmlResult = xmlParse(cfhttp.fileContent)>

<cfoutput>
<cfdump var="#xmlResult#">
</cfoutput>

I was getting an error message from the dump now it works simply by setting vars instead of pushing the data in the links ... no clue why

<cfset accessCode="Myaccesscode">
<cfset vinTodecode="KMHDM***********">
<cfset reportType="2">
<cfset vinqueryURL ="http://www.thedomain.com/ws.aspx?accessCode="
&#accessCode#&"&vin="&#vinTodecode#&"&reportType="&#reportType#>

Now that this is working i need to be able to isolate each field without using the dump.  Can you help me with that ?

[ilssac]

Without knowing what your XML structure looks like, I can only give general advice.

As I said the easiest way is to just directly access the desired nodes with array notation.

xmlResult.rootNode.childNode.grandChild.xmlText

OR

xmlResult['rootNode']['childNode']['grandChild']['xmlText']

You may also deal with node arrays.

xmlResult.rootNode.xmlChildren[0].xmlText

xmlResult.rootNode.xmlChildren[1].....

xmlResult.rootNode.xmlChildren[2].....

You may also need to work with the xmlAttributes nodes to get some of the data.

You can use loops and variables to control this.

If you just look at the XML data as a large "hash" array aka structure it is pretty easy to minipulate.

You can also use xmlSearch() and xmlTransform to use xPath and XSLT syntax to manipulate the XML data into other data forms.

If you can provide an small example of what your xml data looks like we could give you more concrete examples.

[Gear_Head]

Each request for a VIN gives back the information for 1 car only, the  actuel XML has over 180 field I need  like 80 out of those and fill out our form to insert it into our MSSQL database with information that we will provide that are not in the XML file like price, warrenty  etch...

So here is the short XML :

  <?xml version="1.0" encoding="utf-8" standalone="yes" ?>
- <VINquery Version="1.0.0" Report_Type="EXTENDED" Date="10/21/2009">
- <VIN Number="WBAEU3343*********" Status="SUCCESS">
- <Vehicle VINquery_Vehicle_ID="29918" Model_Year="2005" Make="BMW" Model="3-Series" Trim_Level="325xi Sedan">
  <Item Key="VINquery_Vehicle_ID" Value="29918" Unit="" />
  <Item Key="Model Year" Value="2005" Unit="" />
  <Item Key="Make" Value="BMW" Unit="" />
  <Item Key="Model" Value="3-Series" Unit="" />
  <Item Key="Trim Level" Value="325xi Sedan" Unit="" />
  <Item Key="Manufactured in" Value="GERMANY" Unit="" />
  <Item Key="Production Seq. Number" Value="R18010" Unit="" />
  <Item Key="Body Style" Value="SEDAN 4-DR" Unit="" />
  <Item Key="Engine Type" Value="2.5L L6 DOHC 24V" Unit="" />
  <Item Key="Transmission-short" Value="5A | 5M" Unit="" />
  <Item Key="Transmission-long" Value="5-Speed Automatic | 5-Speed Manual" Unit="" />
  <Item Key="Driveline" Value="AWD" Unit="" />
  <Item Key="Tank" Value="16.60" Unit="gallon" />
  <Item Key="Fuel Economy-city" Value="19" Unit="miles/gallon" />
  <Item Key="Fuel Economy-highway" Value="26 - 27" Unit="miles/gallon" />
  <Item Key="Anti-Brake System" Value="4-Wheel ABS" Unit="" />
  <Item Key="Steering Type" Value="R&P" Unit="" />
  <Item Key="Front Brake Type" Value="Disc" Unit="" />
  <Item Key="Rear Brake Type" Value="Disc" Unit="" />
  <Item Key="Turning Diameter" Value="35.80" Unit="in." />
  <Item Key="Front Suspension" Value="Ind" Unit="" />
  <Item Key="Rear Suspension" Value="Ind" Unit="" />
  <Item Key="Front Spring Type" Value="Coil" Unit="" />
  <Item Key="Rear Spring Type" Value="Coil" Unit="" />
  <Item Key="Tires" Value="205/55R16" Unit="" />
  <Item Key="Front Headroom" Value="37.00" Unit="in." />
  <Item Key="Rear Headroom" Value="37.40" Unit="in." />
  <Item Key="Front Legroom" Value="41.40" Unit="in." />
  <Item Key="Rear Legroom" Value="34.40" Unit="in." />
  <Item Key="Front Shoulder Room" Value="54.40" Unit="in." />
  <Item Key="Rear Shoulder Room" Value="54.20" Unit="in." />
  <Item Key="Front Hip Room" Value="No data" Unit="in." />
  <Item Key="Rear Hip Room" Value="No data" Unit="in." />
  <Item Key="Interior Trim" Value="Black Leather Interior | Black Leatherette Interior | Gray Leather Interior | Natural Brown Leather Interior | Sand Leather Interior | Sand Leatherette Interior" Unit="" />
  <Item Key="Exterior Color" Value="Alpine White | Black Sapphire Metallic | Electric Red | Grey Green Metallic | Jet Black | Mystic Blue Metallic | Orient Blue Metallic | Oxford Green Metallic | Silver Gray Metallic | Steel Blue Metallic | Titanium Silver Metallic" Unit="" />
  <Item Key="Curb Weight-automatic" Value="3527" Unit="lbs" />

  <Item Key="Tow Hitch Receiver" Value="N/A" Unit="" />
  <Item Key="Towing Preparation Package" Value="N/A" Unit="" />
  </Vehicle>
  </VIN>
  </VINquery>

**********************************************************************************************

In field like these   <Item Key="Exterior Color" Value="Alpine White | Black Sapphire Metallic | Electric Red | Grey Green Metallic | Jet Black | Mystic Blue Metallic | Orient Blue Metallic | Oxford Green Metallic | Silver Gray Metallic | Steel Blue Metallic | Titanium Silver Metallic" Unit="" />

I will also need to make a select <option> box for all the color if you havea  tip on how to do it would be apriciated.

[ilssac · Accepted solution]

Here is some sample code that shows both basic direct array notation and a simple xmlSearch() with a basic xpath.  I don't have the motivation to work up an xmlTransform() example that would use xslt, but there are plenty of tutorials on how to use xslt with xml and the ColdFusion documentation describes how to use the xmlTransform() function to utilize the xslt with the xml.

<cfsavecontent variable="testXML"><?xml version="1.0" encoding="utf-8" standalone="yes" ?>
  <VINquery Version="1.0.0" Report_Type="EXTENDED" Date="10/21/2009">
  <VIN Number="WBAEU3343*********" Status="SUCCESS">
  <Vehicle VINquery_Vehicle_ID="29918" Model_Year="2005" Make="BMW" Model="3-Series" Trim_Level="325xi Sedan">
  <Item Key="VINquery_Vehicle_ID" Value="29918" Unit="" />
  <Item Key="Model Year" Value="2005" Unit="" />
  <Item Key="Make" Value="BMW" Unit="" />
  <Item Key="Model" Value="3-Series" Unit="" />
  <Item Key="Trim Level" Value="325xi Sedan" Unit="" />
  <Item Key="Manufactured in" Value="GERMANY" Unit="" />
  <Item Key="Production Seq. Number" Value="R18010" Unit="" />
  <Item Key="Body Style" Value="SEDAN 4-DR" Unit="" />
  <Item Key="Engine Type" Value="2.5L L6 DOHC 24V" Unit="" />
  <Item Key="Transmission-short" Value="5A | 5M" Unit="" />
  <Item Key="Transmission-long" Value="5-Speed Automatic | 5-Speed Manual" Unit="" />
  <Item Key="Driveline" Value="AWD" Unit="" />
  <Item Key="Tank" Value="16.60" Unit="gallon" />
  <Item Key="Fuel Economy-city" Value="19" Unit="miles/gallon" />
  <Item Key="Fuel Economy-highway" Value="26 - 27" Unit="miles/gallon" />
  <Item Key="Anti-Brake System" Value="4-Wheel ABS" Unit="" />
  <Item Key="Steering Type" Value="R&P" Unit="" />
  <Item Key="Front Brake Type" Value="Disc" Unit="" />
  <Item Key="Rear Brake Type" Value="Disc" Unit="" />
  <Item Key="Turning Diameter" Value="35.80" Unit="in." />
  <Item Key="Front Suspension" Value="Ind" Unit="" />
  <Item Key="Rear Suspension" Value="Ind" Unit="" />
  <Item Key="Front Spring Type" Value="Coil" Unit="" />
  <Item Key="Rear Spring Type" Value="Coil" Unit="" />
  <Item Key="Tires" Value="205/55R16" Unit="" />
  <Item Key="Front Headroom" Value="37.00" Unit="in." />
  <Item Key="Rear Headroom" Value="37.40" Unit="in." />
  <Item Key="Front Legroom" Value="41.40" Unit="in." />
  <Item Key="Rear Legroom" Value="34.40" Unit="in." />
  <Item Key="Front Shoulder Room" Value="54.40" Unit="in." />
  <Item Key="Rear Shoulder Room" Value="54.20" Unit="in." />
  <Item Key="Front Hip Room" Value="No data" Unit="in." />
  <Item Key="Rear Hip Room" Value="No data" Unit="in." />
  <Item Key="Interior Trim" Value="Black Leather Interior | Black Leatherette Interior | Gray Leather Interior | Natural Brown Leather Interior | Sand Leather Interior | Sand Leatherette Interior" Unit="" />
  <Item Key="Exterior Color" Value="Alpine White | Black Sapphire Metallic | Electric Red | Grey Green Metallic | Jet Black | Mystic Blue Metallic | Orient Blue Metallic | Oxford Green Metallic | Silver Gray Metallic | Steel Blue Metallic | Titanium Silver Metallic" Unit="" />
  <Item Key="Curb Weight-automatic" Value="3527" Unit="lbs" />
  <Item Key="Tow Hitch Receiver" Value="N/A" Unit="" />
  <Item Key="Towing Preparation Package" Value="N/A" Unit="" />
  </Vehicle>
  </VIN>
  </VINquery>
</cfsavecontent>

<cfset xmlObj = xmlParse(testXML)>
<cfoutput>
     #xmlObj.VINquery.vin.vehicle.xmlAttributes.make#
     #xmlObj.VINquery.vin.vehicle.xmlAttributes.model#
     #xmlObj.VINquery.vin.vehicle.xmlAttributes.model_year#
     #xmlObj.VINquery.vin.vehicle.xmlAttributes.trim_level#
      #xmlObj.VINquery.vin.vehicle.xmlAttributes.vinquery_vehicle_id#
  <hr>
     #xmlObj.VINquery.vin.vehicle.item[1].xmlAttributes.key#
     #xmlObj.VINquery.vin.vehicle.item[1].xmlAttributes.unit#     
  #xmlObj.VINquery.vin.vehicle.item[1].xmlAttributes.value#
  <hr>
     #xmlObj.VINquery.vin.vehicle.item[2].xmlAttributes.key#
     #xmlObj.VINquery.vin.vehicle.item[2].xmlAttributes.unit#     
  #xmlObj.VINquery.vin.vehicle.item[2].xmlAttributes.value#
</cfoutput>

<cfset items = xmlSearch(xmlObj,"//Item")>

<cfoutput>
<cfloop array="#items#" index="elem">
#elem.xmlName# #elem.xmlAttributes.key# #elem.xmlAttributes.unit# #elem.xmlAttributes.value#<br>
</cfloop>
</cfoutput>

<cfdump var="#items#">
<cfdump var="#xmlObj#">

<Item Key="Exterior Color" Value="Alpine White | Black Sapphire
Metallic | Electric Red | Grey Green Metallic | Jet Black | Mystic Blue
Metallic | Orient Blue Metallic | Oxford Green Metallic | Silver Gray
Metallic | Steel Blue Metallic | Titanium Silver Metallic" Unit="" />

That is just a list delimited by the pipe "|" character.  Check out the list functions http://livedocs.adobe.com/coldfusion/8/htmldocs/functions-pt0_13.html

[Gear_Head]

Hi again, they just decided to go with another VIn decoder company and now i got a new structure to work with the absic stays the same but i cant get the info to show I dont know what to do with the S: in teh XML can you check this out please. I put the XML in a file so you can see it.

<cfset vinqueryURL="http://www.thedomain.com/car.xml">

<cfhttp url="#vinqueryURL#" charset="utf-8">
<cfif not isXML(cfhttp.fileContent)>
<cfthrow message="Query web service did not return valid XML.">
</cfif>
<cfset xmlResult = xmlParse(cfhttp.fileContent)>

<cfoutput>

  <hr>

#xmlResult.VehicleInformation.XmlAttributes.makename#  <--- thats my problem

  <hr>

</cfoutput>

Thanks again

[ilssac]

That 's' is called a namespace and it chages the options you have to manipulate the xml data.

For direct access you have to use bracket AKA array notation.  #aVar['aNode']['bNode']['cNode'].

If you use xPath or XSLT techniques you need to account for the namespace when using these features.

Here is a modification of my previous example that takes the namespace into account.

<cfoutput>
    #xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["vin"]["xmlText"]#
<hr>
<cfset options = xmlSearch(xmlObj,"//*[local-name()='factoryOptions']")>
<cfloop array="#options#" index="option">
  <strong>#option.xmlName#</strong><br>
  <cfloop from="1" to="#arrayLen(option.xmlChildren)#" index="c">
  #option.xmlChildren.xmlName# - #option.xmlChildren.xmlText#<br>
  </cfloop>
  <hr>
</cfloop>
</cfoutput>

[Gear_Head]

Thank you so much Ian its working.

But  🙂  I stumbled on another problem when i query the VIN decoder to get my information i need to save it on our system to make it faster with some other internal specification that we need like:  ( stock_id, price, milage ... etch. )

I think there is 2 way to do this :

1. Enter all the data i need in fields in our system for all 200 fields (the long way)

But i found a problem when i get information from them for a  field like :

#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["styles"]["subdivisionName"]#

Result should be : Chevy Utility Vehicles

But it showsup like this :  <?xml version="1.0" encoding="UTF-8"?><subdivisionName xmlns="urn:description6.kp.thedomain.com">Chevy Utility Vehicles</subdivisionName>

So i cant write that in my database

2. Save all the data i need in a XML file with the #stock_id# has a file name, but then again the XML file i will save wit  still have the query to there system  ... right ?

What does that do ?

<subdivisionName xmlns="urn:description6.kp.thedomain.com">Chevy Utility Vehicles</subdivisionName>

And can i save the XML file and if you how ?

here is my email gates@securenet.net i need to offer you something email me directly.

[ilssac]

Good Monday Morning hope your weekend was well.

Gear_Head wrote:

#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["styles"]["subdivisionName "]#

Result should be : Chevy Utility Vehicles

But it showsup like this :  <?xml version="1.0" encoding="UTF-8"?><subdivisionName xmlns="urn:description6.kp.thedomain.com">Chevy Utility Vehicles</subdivisionName>

#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["styles"]["subdivisionName"]# This will return an XML Node which is what you have above with the <?xml ....> prolog and all that.

The XMLText node returns the value of a node.

#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["styles"]["subdivisionName"]["XMLText"]#

P.S. You can mix and match array and dot notion if you care.

#xmlObj["s:envelope"]["s:body"].vehicleInformation.styles.subdivisionName#

#xmlObj["s:envelope"]["s:body"].vehicleInformation.styles.subdivisionName.XMLText#

[Gear_Head]

Oooooooh

I get it thank you so much and i also manage to save the XML content on my server for each VIN request, this is awsome thank you.

I will of course stumble on other problems very soon hope you are stil available 🙂

[Gear_Head]

Told you,

i'm trying to get how many options there is for lets say Standards to know how long the loop has to go can you tell me what i'm doing wrong.

<cfset options = xmlSearch(xmlObj,"*/*/*/*/["standards"]")>

<cfloop index="Loopcount" From="1" to="????????">

<cfset installedvalue = '#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"][Loopcount]["installed"]["XMLText"]#'>

<cfif installedvalue EQ 'true'> #installedvalue# : #xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"][Loopcount]["description"]["XMLText"]#</cfif>

</cfloop>

[ilssac]

xmlSearch returns an array.  The arrayLen() functions will tell you how large an array is.

<cfloop index="Loopcount" From="1" to="#arrayLen(options)#">

[Gear_Head]

I know about that part hre is my real code :

<cfset options = xmlSearch(xmlObj,"*/*/*/*/["standards"]")>

<hr>
<cfloop index="Loopcount" From="1" to="#arrayLen(options)#">

<cfset installedvalue = '#xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"][Loopcount][ "installed"]["XMLText"]#'>
<cfif installedvalue EQ 'true'> #installedvalue# : #xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"][Loopcount][" description"]["XMLText"]#</cfif>

</cfloop>

Here is the error message i get

Invalid CFML  construct found on line 21 at column 46.
ColdFusion was looking at the  following text: standards The CFML compiler was processing: an expression beginning with "xmlSearch", on line 21, column 18.This message  is usually caused by a problem in the expressions structure. a cfset tag beginning on line 21, column 2.
The  error occurred in C:\**************\**vin.cfm: line  21
19 : 20 : 21 : <cfset options = xmlSearch(xmlObj,"*/*/*/*/["standards"]")> 22 : 23 :  <hr>

[ilssac]

If you ever need to nest qoutes like that you need to alternate double and singe quotes.  The proper syntax for what you tried would have been.

<cfset options = xmlSearch(xmlObj,"*/*/*/*/['standards']")>

OR

<cfset options = xmlSearch(xmlObj,'*/*/*/*/["standards"]')>

But this would not return what you want.  xmlSearch uses XPATH syntax which is different from ColdFusion's array notation syntax.

Your search should actually look something like.

<cfset options = xmlSearch(xmlObj,"/*/*/*/*[local-name()='standards']")>

OR just

<cfset options = xmlSearch(xmlObj,"//*[local-name()='standards']")>

Message was edited by: Ian Skinner Don't forget the <strong>local-name()</strong> xpath function to account for the namespaces in your XML.

[Gear_Head]

Hi again,

They just switch me to a web service, I played with it a little but now i got a new problem, I can get the data but i cant find a way to wrtite the file to a XML document.

When i was calling thre service i used

<cfset xmlObj = xmlParse(cfhttp.fileContent)>

<cfset XMLText=ToString(xmlObj)>
<cffile action="write" file="C:\inetpub\www\mystuff\#VIN#.xml" output="#XMLText#">

To write the XML file from that vin number but with a Web service i cant figure out how to doit.

This is what the service call looks like :

<cfscript>

    webService = createObject("webservice", "myservicename");

    countryCode = "CA";
    languageCode = "fr";
    accountInfo = StructNew();
    accountInfo.accountNumber = "myaccount";
    accountInfo.accountSecret = "mypassword";
    accountInfo.locale = StructNew();
    accountInfo.locale.language = languageCode;
    accountInfo.locale.country = countryCode;

    version = "";
    dataVersionsRequest = StructNew();
    dataVersionsRequest.accountInfo = accountInfo;
    dataVersions = webService.getDataVersions(dataVersionsRequest);
    for (i = 1; i LTE ArrayLen(dataVersions); i=i+1) {
        if (dataVersions.country EQ "US"){
            version = dataVersions.country & " " & dataVersions.build & " (" & DateFormat(dataVersions.date) & " " & TimeFormat(dataVersions.date) & ")";
        }
    }

</cfscript>

Does this create a new Web service everytime ?

I know it creates an object but how do i save it to ans XML file ?

I sent you the entire sample file in a PM.

The data i get is the same then in the previous XML file.

[ilssac]

You are mixing and matching two techniques unecessarily, either technique would give you the results.

<cfset options = xmlSearch(xmlObj,"*/*/*/*/["standards"]")>
<cfoutput>
<cfloop index="loopcount" from="1" to="#arrayLen(options)#">
#options[loopcount].installed.xmlText#
</cfloop>

OR

<cfoutput>
<cfloop index="loopCount" from="1" to="#arrayLen(xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"].xmlChildren)#">
##xmlObj["s:envelope"]["s:body"]["vehicleInformation"]["standards"][Loopcount][ "installed"]["XMLText"]#
</cfloop>

These both should return the same information.