XML to Query

[TommyMTL]

This code is reading an XML document:

<cffile action="read" file="C:\Documents and Settings\Administrator\Application Data\pbx\extensions\table.xml" variable="myxml">
<cfset mydoc = XmlParse(myxml)>
<!--- SET ARRAY FROM table.xml file --->
<cfset XMLSourceFile = mydoc.table.XmlChildren>
<cfset MyList = ArrayToList(XMLSourceFile, " ")>
<cfdump var="#XMLSourceFile#">

the structure of my xml document is:
<?xml version="1.0" encoding="UTF-8"?>
<table>
<row>1</row>
<row>2</row>
<row>3</row>


I need to pass all <ROW> ChildNode to a query but just the text content of it only.
so it should be 1, 2, 3

So fare it is giving me:
<?xml version="1.0" encoding="UTF-8"?> <row>1</row>
<?xml version="1.0" encoding="UTF-8"?> <row>2</row>
<?xml version="1.0" encoding="UTF-8"?> <row>3</row>

so I should be able to get 1,2,3 only without the <?xml version="1.0" encoding="UTF-8"?>.

How ould I do that.

many thanks!
:-)

[BKBK]

You're almost there. XML elements are, strictly speaking, not arrays even though you may apply array functions to them. So, beware of strange results. As you have discovered, for example, your MyList variable turns out to be <?xml version="1.0" encoding="UTF-8"?> <row>1</row> <?xml version="1.0" encoding="UTF-8"?> <row>2</row> <?xml version="1.0" encoding="UTF-8"?> <row>3</row> ...

To get the content you want, you have to dig down to the individual elements and get out the XMLText, thus

<cffile action="read" file="C:\Documents and Settings\Administrator\Application Data\pbx\extensions\table.xml" variable="myxml">
<cfset mydoc = XmlParse(myxml)>
<cfset myList = "">
<cfloop from="1" to="#arrayLen(mydoc.XmlRoot.XmlChildren)#" index="n">
<!--- Default delimiter (comma) implied --->
<cfset myList = listAppend(myList,mydoc.XmlRoot.XmlChildren.XmlText)>
</cfloop>

myList dump: <cfdump var="#myList#"><br>
listElement2: <cfoutput>#listGetAt(myList,2)#</cfoutput>

[TommyMTL]

BKBK, thanks for the tip,

I get the following error:
Element XMLROOT.XMLCHILDREN.XMLTEXT is undefined in MYDOC.

[BKBK]

I get the following error: Element XMLROOT.XMLCHILDREN.XMLTEXT is undefined in MYDOC.

Look at it again. I later changed the code from children[letter i] to children. The [letter i] disappeared because this forum's parser translates [letter i] as an italic display tag.

[TommyMTL]

Fantastic, thanks a lot,

My next step,
I need to read a different XML document with the following code:

<!--- Read the file and convert it to an XML document object --->
<cfif IsDefined ("URL.Target") and URL.Target NEQ "">
<cffile action="read" file="C:\Documents and Settings\Administrator\Application Data\pbx\#URL.Action#\#URL.Target#" variable="myxml">
<cfset mydoc = XmlParse(myxml)>

<cfset emp = mydoc.row.XmlChildren>
<cfset size = ArrayLen(emp)>

<cfset myquery = QueryNew(#MyList#)>
<cfset temp = QueryAddRow(myquery)>
<cfloop index="i" from = "1" to = "1">
<cfset temp = QuerySetCell(myquery, "admin", #mydoc.row.admin.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "anonymous", #mydoc.row.anonymous.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "auth_adr", #mydoc.row.auth_adr.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "barge", #mydoc.row.barge.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "block_cid", #mydoc.row.block_cid.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "call_redial", #mydoc.row.call_redial.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "call_return", #mydoc.row.call_return.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "cfa", #mydoc.row.cfa.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "cfb", #mydoc.row.cfb.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "cfn", #mydoc.row.cfn.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "cfn_timeout", #mydoc.row.cfn_timeout.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "dial_plan", #mydoc.row.dial_plan.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "display_name", #mydoc.row.display_name.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "dnd", #mydoc.row.dnd.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "email_address", #mydoc.row.email_address.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "email_cdr", #mydoc.row.email_cdr.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "email_missed", #mydoc.row.email_missed.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "email_vmail", #mydoc.row.email_vmail.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "fax", #mydoc.row.fax.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "has_name", #mydoc.row.has_name.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "listen", #mydoc.row.listen.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "log_register", #mydoc.row.log_register.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mac", #mydoc.row.mac.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mailbox_access", #mydoc.row.mailbox_access.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mb_enable", #mydoc.row.mb_enable.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mb_pin", #mydoc.row.mb_pin.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mb_size", #mydoc.row.mb_size.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mb_timeout", #mydoc.row.mb_timeout.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "mwi", #mydoc.row.mwi.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "name_use", #mydoc.row.name_use.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "password", #mydoc.row.password.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "reading", #mydoc.row.reading.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "teach", #mydoc.row.teach.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "ua_type", #mydoc.row.ua_type.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "vm_indicator", #mydoc.row.vm_indicator.XmlText#, #i#)>
<cfset temp = QuerySetCell(myquery, "wavname", #mydoc.row.wavname.XmlText#, #i#)>
</cfloop>
</cfif>


#MyList# variable is giving me waht I should be reading from the following file, this is working so fare, bu8t I need to fin a way to make all

<cfset temp = QuerySetCell(myquery, "admin", #mydoc.row.admin.XmlText#, #i#)>

to something like

<cfset temp = QuerySetCell(myquery, "#MyList#", #mydoc.row.#MyList#.XmlText#, #i#)>
where each element from MyList become a temp varable.

The idea is that I have a XML file used has a template for generating various XML document and then read all the documents created based on that template.

so many thanks!