Known Participant

Answered

XML file Query

Forum|Forum|16 years ago
October 22, 2009
2 replies
5289 views

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>

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.

Database access

This topic has been closed for replies.

Correct answer ilssac

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" />

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

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.

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

ilssac

Inspiring

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_HeadAuthor

Known Participant

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

Inspiring

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#

ilssac

Inspiring

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_HeadAuthor

Known Participant

I get an error message what m i doing wrong ?

]]>

#xmlResult#

</cfoutput

ilssac

Inspiring

What error message did you get?

What did you actually do?

Your message may have gotten truncated, it looks incomplete.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded