Loop through recordset to output XML

shakshakshak · April 20th, 2011, 09:21 AM

Hi everyone,

I need to generate an XML file from the 2 tables below:

dbo.Guides - ID (int), Title (varchar)

dbo.Steps - ID (int), GuideID (int), Info (varchar)

Each guide has many steps which are linked by dbo.Guides.ID and dbo.Steps.GuideID

Here is my current attempt (recordsets are [1] SELECT * FROM dbo.guides [2] SELECT * FROM dbo.steps):

Code:

<?xml version="1.0" encoding="ISO-8859-1"?>
<internal_guides>
<% While (Not guides.EOF) %>
	<guide>
		<id><![CDATA[<%=(guides.Fields.Item("ID").Value)%>]]></id>
		<title><![CDATA[<%=(guides.Fields.Item("Title").Value)%>]]></title>
<% Do While (steps.Fields.Item("GuideID").Value) = (guides.Fields.Item("ID").Value) %>
		<step><![CDATA[<%=(steps.Fields.Item("Info").Value)%>]]></step>
<% steps.MoveNext()
	Loop %>
	</guide>
<% guides.MoveNext()
	Wend %>
</internal_guides>
<%
guides.Close()
	Set guides= Nothing
steps.Close()
	Set steps = Nothing
%>

It outputs the XML OK if I remove the Do While... Loop and <steps> node, but when the Do While... Loop and <steps> node are present I get 'XML Parsing Error: not well-formed'.

I'm by no means an expert ASP guru, I'm hoping one of you experts can help on this as it is pretty urgent.

Thanks for any help you can give, it is much appreciated!! :)

om_prakash · April 22nd, 2011, 04:43 AM

I think you should add one more tag for Steps. Example:

Code:

<% While (Not guides.EOF) %>
	<guide>
		<id><![CDATA[<%=(guides.Fields.Item("ID").Value)%>]]></id>
		<title><![CDATA[<%=(guides.Fields.Item("Title").Value)%>]]></title>
<steps>
<% Do While (steps.Fields.Item("GuideID").Value) = (guides.Fields.Item("ID").Value) %>
		<step><![CDATA[<%=(steps.Fields.Item("Info").Value)%>]]></step>
<% steps.MoveNext()
</steps>
 Loop %>
	</guide>

whiterainbow · April 25th, 2011, 11:04 AM

As well check for the special characters in the title that breaks the formation of the XML string.