Loop through recordset to output XML

[shakshakshak]

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):

<?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!! :)

[hielo]

try:

<?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>
		<%
		If NOT steps.EOF Then
			steps.moveFirst()
			While (NOT steps.EOF) 
				If (steps.Fields.Item("GuideID").Value) = (guides.Fields.Item("ID").Value) Then
				%>
					<step><![CDATA[<%=(steps.Fields.Item("Info").Value)%>]]></step>
				<%
				End If
				steps.moveNext()
			Wend
		End If
%>
	</guide>
<% 
	guides.MoveNext()
Wend 
%>
</internal_guides>
<%
guides.Close()
Set guides= Nothing

steps.Close()
Set steps = Nothing
%>

[shakshakshak]

I'll try that now, thanks for the quick reply Hielo, I've been trying since I made the post :(

[shakshakshak]

Hi,

I tried that exactly as you said, it returns the all steps for the first record but ono steps for subsequent records. Thanks for the help, do you know what could be happening?

Thanks :)

[hielo]

try moving line 9 to the end of the loop:

<?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>
		<%
		If NOT steps.EOF Then

			While (NOT steps.EOF) 
				If (steps.Fields.Item("GuideID").Value) = (guides.Fields.Item("ID").Value) Then
				%>
					<step><![CDATA[<%=(steps.Fields.Item("Info").Value)%>]]></step>
				<%
				End If
				steps.moveNext()
			Wend
                        steps.moveFirst()
		End If
%>
	</guide>
<% 
	guides.MoveNext()
Wend 
%>
</internal_guides>
<%
guides.Close()
Set guides= Nothing

steps.Close()
Set steps = Nothing
%>

[shakshakshak]

Hielo, thankyou!

It's now outputting all steps for all guides. You've been a great help mate and quick too, thanks again...

:):):)