Ask Ben: Parsing A Microsoft XML Rowset Into A ColdFusion Query

<!---
	Store the XML response into a ColdFusion XML object. I am
	explicity showing the XML here, but you probably would end
	up doing something like:

	<cfset xmlResponse = XmlParse( CFHTTP.FileContent ) />
--->
<cfxml variable="xmlResponse">

	<xml
		xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
		xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
		xmlns:rs="urn:schemas-microsoft-com:rowset"
		xmlns:z="#RowsetSchema">

		<!--- Define the rowset column schema. --->
		<s:Schema id="RowsetSchema">

			<s:ElementType
				name="row"
				content="eltOnly"
				rs:CommandTimeout="30">

				<!--- Define first column. --->
				<s:AttributeType
					name="auctionDate"
					rs:number="1"
					rs:nullable="true">

					<s:datatype
						dt:type="string"
						dt:maxLength="10"
						/>

				</s:AttributeType>

				<!--- Define second column. --->
				<s:AttributeType
					name="Auction"
					rs:number="2"
					rs:nullable="true"
					rs:writeunknown="true">

					<s:datatype
						dt:type="string"
						rs:dbtype="str"
						dt:maxLength="20"
						/>

				</s:AttributeType>

				<!--- Define third column. --->
				<s:AttributeType
					name="lotNumber"
					rs:number="3"
					rs:writeunknown="true">

					<s:datatype
						dt:type="int"
						dt:maxLength="4"
						rs:precision="10"
						rs:fixedlength="true"
						rs:maybenull="false"
						/>

				</s:AttributeType>

			</s:ElementType >

		</s:Schema>


		<!--- Define the row data itself. --->
		<rs:data>

			<z:row
				auctionDate="2007/11/03"
				Auction="ABC"
				lotNumber="1234"
				/>

			<z:row
				auctionDate="2007/12/05"
				Auction="XYZ"
				lotNumber="6789"
				/>

		</rs:data>

	</xml>

</cfxml>

<!---
	Create our resultant query. We are going to start off with
	an empty query because we don't know how the columns are
	going to be named or typed.
--->
<cfset qResponse = QueryNew( "" ) />

<!---
	Create an array to hold the mapping of column data types
	to JavaCast() types. This will come into play later when
	we poopulate the query.
--->
<cfset arrDataMap = ArrayNew( 1 ) />

<!---
	Create a column name map. This will come into play later
	when we populate the query.
--->
<cfset arrColumnMap = ArrayNew( 1 ) />


<!---
	Get the column names. From this node list, we will
	construct our query columns. Because we want to try and
	properly type our query columns, let's only get column
	names that have a nested data type node.
--->
<cfset arrColumnNames = XmlSearch(
	xmlResponse,
	"//s:ElementType[ @name = 'row' ]" &
	"/s:AttributeType[ s:datatype[ @dt:type ] ]/@name/"
	) />

<!---
	Now, let's get the list of data types for the columns.
	This should give us an array of nodes that corresponds
	to the column name node list gotten above.
--->
<cfset arrDataTypes = XmlSearch(
	xmlResponse,
	"//s:ElementType[ @name = 'row' ]" &
	"/s:AttributeType/s:datatype/@dt:type/"
	) />


<!---
	ASSERT: At this point, we should have two node arrays of
	equal length. One will hold the column name, the other will
	hold the column type.
--->


<!--- Loop over the column values. --->
<cfloop
	index="intColumnIndex"
	from="1"
	to="#ArrayLen( arrColumnNames )#"
	step="1">

	<!--- Get the name of the column. --->
	<cfset strName = arrColumnNames[ intColumnIndex ].XmlValue />

	<!--- Get the data type of the column. --->
	<cfset strType = arrDataTypes[ intColumnIndex ].XmlValue />


	<!---
		Now, it's not enough to have just gotten the data type
		from the query schema; it needs to match up with the
		types that are allowed in the ColdFusion query (and
		underalying Java record set).

		Furthermore, we need to figure out out Data Map so that
		when we go to populate the query, we will know what type
		to cast to when using JavaCast().
	--->
	<cfswitch expression="#strType#">

		<cfcase value="int">

			<!--- Set the column type. --->
			<cfset strType = "cf_sql_integer" />

			<!--- Set the data map type. --->
			<cfset arrDataMap[ intColumnIndex ] = "int" />

		</cfcase>

		<!---
			Be default, if we did not match the type, then
			just store as a string, which can handle the most
			data types.
		--->
		<cfdefaultcase>

			<!--- Set the column type. --->
			<cfset strType = "cf_sql_varchar" />

			<!--- Set the data map type. --->
			<cfset arrDataMap[ intColumnIndex ] = "string" />

		</cfdefaultcase>
	</cfswitch>


	<!--- Add the name to the column map. --->
	<cfset arrColumnMap[ intColumnIndex ] = strName />


	<!--- Add the new query column. --->
	<cfset QueryAddColumn(
		qResponse,
		strName,
		strType,
		ArrayNew( 1 )
		) />

</cfloop>


<!---
	ASSERT: At this point, we have constructed our ColdFusion
	query data object with the proper column names and data
	types. The query, however is empty.

	We also have a column map and a data map populated based
	on the index / order in which we found the columns.
--->


<!---
	Now, we want to work on populating the query with the
	returned data. Let's query for all the data nodes. To get
	the row nodes, we have to use the local-name() rather than
	the prefixed value, z:row.

	NOTE: I am not sure why the prefixed name doesn't work. It
	seems to have something to do with the # in the xml name
	space definition. If you remove the # in front of
	RowsetSchema, then it works, but since we are trying not to
	alter the response, let's go with local-name().
--->
<cfset arrRowNodes = XmlSearch(
	xmlResponse,
	"//rs:data/*[ local-name() = 'row' ]/"
	) />


<!--- Loop over the row data. --->
<cfloop
	index="intRowIndex"
	from="1"
	to="#ArrayLen( arrRowNodes )#"
	step="1">

	<!--- Add a row to our response query. --->
	<cfset QueryAddRow( qResponse ) />

	<!--- Get a short hand reference to the row. --->
	<cfset objRow = arrRowNodes[ intRowIndex ] />


	<!--- Loop over the column map. --->
	<cfloop
		index="intColumnIndex"
		from="1"
		to="#ArrayLen( arrColumnMap )#"
		step="1">

		<!--- Get a short hand to the column name. --->
		<cfset strName = arrColumnMap[ intColumnIndex ] />

		<!--- Get a short hand to the data type. --->
		<cfset strType = arrDataMap[ intColumnIndex ] />


		<!---
			Check to see if this row has the given column value
			(represented as an attribute of the row node).
		--->
		<cfif StructKeyExists(
			objRow.XmlAttributes,
			strName
			)>

			<!---
				The attribute exists. Now, let's store it into
				the query using the given data type mapping.
			--->
			<cfset qResponse[ strName ][ qResponse.RecordCount ] =
				JavaCast(
					strType,
					objRow.XmlAttributes[ strName ]
					) />

		<cfelse>

			<!---
				The attribute did not exist. Store this
				value as an explicity null (even though the
				query object will do this on it's own, it's
				nice to be explicit.
			--->
			<cfset qResponse[ strName ][ qResponse.RecordCount ] =
				JavaCast( "null", 0 )
				/>

		</cfif>

	</cfloop>

</cfloop>


<!--- Dump out query. --->
<cfdump
	var="#qResponse#"
	label="Query From Microsoft RowSet Schema"
	/>