Solved

Create XML document with asp

Posted on 2006-10-25
6
316 Views
Last Modified: 2006-11-18
Hi Experts,

I have the following code, What I am trying to achieve wherever the usedate matches generate product list and loop - but I am only getting on use date
Please take a look at the code and let me know what I am doing wrong

***************************************
Begin Code
***************************************
<%@ Language=VBScript %>
<!-- #INCLUDE FILE="incwofunctions.asp" -->
<!-- #INCLUDE FILE="csuitefn.asp" -->
<!-- #INCLUDE FILE="incdb.asp" -->
<%
'Request the current year from querystring
Dim dtmStartDate, dtmEndDate

dtmStartDate = "9/01/2006"
If Request("StartDate") <> "" then dtmStartDate = Request("StartDate")
dtmEndDate = "10/30/2006"
If Request("EndDate") <> "" then dtmEndDate = Request("EndDate")

Dim oXmlDate
oXmlDate = ewZeroPad(Month(Date), 2) & ewZeroPad(Day(Date), 2) & ewZeroPad(Year(Date), 4)

'Set the database objects
Dim oRsDate, oRsProducts, strSQL, intCounter, strXML, strUseDateXML, strUseLocationXML, strProductsXML

intCounter = 0
intCounter2 = 8
strUseDateXML = ""
strUseLocationXML = ""
strProductsXML = ""

'Database objects
Set oRsDate = Server.CreateObject("ADODB.Recordset")
Set oRsProducts = Server.CreateObject("ADODB.Recordset")

'Submission'
strXML = "<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>" & VbCrLf
strXML = strXML & "<Submission version=""1.0"">" & VbCrLf
'UseReport
strXML = strXML & "<UseReport id=" & chr(34) & "Blooming" & xmlDate & chr(34) & ">" & VbCrLf
strXML = strXML & "<SiteCategory>Agriculture</SiteCategory>" & VbCrLf
strXML = strXML & "<SpecificSite>Nursery/Christmas Trees</SpecificSite>" & VbCrLf
GetRSFromSpwithParam "spGetwoSAOregonPursDate '" & dtmStartDate & "','" & dtmEndDate & "'", oRsDate
If Not oRsDate.EOF then
      While Not oRsDate.EOF
            'Use Date
            strUseDateXML = "<useDate>"
            strUseDateXML = strUseDateXML & oRsDate("dtmDateApplied") & "</useDate>" & VbCrLf
            strUseLocationXML = "<UseLocation>" & VbCrLf
            strUseLocationXML = strUseLocationXML & "<UseLocaionWaterBasin>" & VbCrLf
            strUseLocationXML = strUseLocationXML & "<waterBasin>Willamette</waterBasin>" & VbCrLf
            strUseLocationXML = strUseLocationXML & "</UseLocationWaterBasin>" & VbCrLf
            strUseLocationXML = strUseLocationXML & "</UseLocation>" & VbCrLf
            'Get Product Detail
            GetRSFromSpwithParam "spGetwoSAOregonPURS '" & dtmStartDate & "','" & dtmEndDate & "'",oRsProducts
                  strProductsXML = "<products>" & VbCrLf
                  While Not oRsProducts.EOF
                        If oRsDate("dtmDateApplied") = oRsProducts("dtmDateApplied") Then
                              strProductsXML = strProductsXML & "<UseReportProduct>" & VbCrLf
                              strProductsXML = strProductsXML & "<epaProductNumber>" & oRsProducts("strEPARegNumber").Value & "</epaProductNumber>" & VbCrLf
                              strProductsXML = strProductsXML & "<productName>" & oRsProducts("strChemicalProductName").Value & "</productName>" & VbCrLf
                              strProductsXML = strProductsXML & "<purpose>" & oRsProducts("strTargetTypeAltName").Value & "</purpose>" & VbCrLf
                              strProductsXML = strProductsXML & "<quantity>" & oRsProducts("dblTotalAmount").Value & "</quantity>" & VbCrLf
                              strProductsXML = strProductsXML & "<quantityUnit>" & oRsProducts("strUnitAltName").Value & "</quantityUnit>" & VbCrLf
                              strProductsXML = strProductsXML & "</UseReportProduct>" & VbCrLf
                        End If
                        oRsProducts.MoveNext()
                  Wend
                  strProductsXML = strProductsXML & "</products>" & VbCrLf
                  oRsProducts.MoveFirst()
                  oRsDate.MoveNext()
      Wend
End If

'Concatenate & lose the XML document
strXML = strXML & strUseDateXML &  strUseLocationXML &  strProductsXML & VbCrLf & "</UseReport>" & VbCrLf & "</Submission>"
'Output it
Response.Write(strXML)
'Destroy objects
Set oRsDate = nothing
Set oRsProducts = nothing
%>

**************************************
End Code
*************************************
0
Comment
Question by:krisred
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 9

Expert Comment

by:HappyFunBall
ID: 17804427
Hi,

I noticed this line { strUseDateXML = "<useDate>" } sets the strUseDateXML variable to just "<useDate>" and wipes everything else out every time the loop happens.  That's probably why you're only seeing one <useDate>.

Change the line to a concatenation and that should help:  {strUseDateXML = strUseDateXML & "<useDate>"}
0
 

Author Comment

by:krisred
ID: 17804778
Hi HappyFunBall,

I have changed the line like you asked, but now I am getting all dates in once set -

<useDate>10/2/2006</useDate>
<useDate>10/3/2006</useDate>
<useDate>10/4/2006</useDate>
<useDate>10/5/2006</useDate>
<useDate>10/6/2006</useDate>
<useDate>10/7/2006</useDate>
<useDate>10/10/2006</useDate>
<useDate>10/11/2006</useDate>
<useDate>10/13/2006</useDate>
<useDate>10/14/2006</useDate>
<useDate>10/16/2006</useDate>
<useDate>10/18/2006</useDate>
<useDate>10/20/2006</useDate>
<useDate>10/21/2006</useDate>

What I want for each date - loop through products
<useDate>10/21/2006</useDate>
..products
<useDate>10/21/2006</useDate>
..products

Thanks,

Kris

0
 
LVL 9

Accepted Solution

by:
HappyFunBall earned 500 total points
ID: 17804837
You're on your way but it looks like another problem is that you're concatenating all the strings after the loops have finished.  You'll want to append to your main XML variable (strXML) at the end of each loop, so the dates, locations, and products will appear in the right order.

So add this line after the oRsDate.MoveNext() line:

strXML = strXML & strUseDateXML &  strUseLocationXML &  strProductsXML & VbCrLf

Then change the strXML = line that's just outside of the loop to this:

strXML = strXML & "</UseReport>" & VbCrLf & "</Submission>"
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:krisred
ID: 17804983
That works too.. Thanks!

I just changed to use two varianbles strXML and strDataXML

Here's the final working code:
****
Begin code
****
<%@ Language=VBScript %>
<!-- #INCLUDE FILE="incwofunctions.asp" -->
<!-- #INCLUDE FILE="csuitefn.asp" -->
<!-- #INCLUDE FILE="incdb.asp" -->
<%
'Request the current year from querystring
Dim dtmStartDate, dtmEndDate

dtmStartDate = "10/01/2006"
If Request("StartDate") <> "" then dtmStartDate = Request("StartDate")
dtmEndDate = "10/30/2006"
If Request("EndDate") <> "" then dtmEndDate = Request("EndDate")

Dim oXmlDate
oXmlDate = ewZeroPad(Month(Date), 2) & ewZeroPad(Day(Date), 2) & ewZeroPad(Year(Date), 4)

'Set the database objects
Dim oRsDate, oRsProducts, strSQL, intCounter, strXML, strDataXML

strDataXML = ""

'Database objects
Set oRsDate = Server.CreateObject("ADODB.Recordset")
Set oRsProducts = Server.CreateObject("ADODB.Recordset")

'Submission'
strXML = "<?xml version=""1.0"" encoding=""utf-8"" standalone=""yes""?>" & VbCrLf
strXML = strXML & "<Submission version=""1.0"">" & VbCrLf
'UseReport
strXML = strXML & "<UseReport id=" & chr(34) & "Blooming" & oXmlDate & chr(34) & ">" & VbCrLf
strXML = strXML & "<SiteCategory>Agriculture</SiteCategory>" & VbCrLf
strXML = strXML & "<SpecificSite>Nursery/Christmas Trees</SpecificSite>" & VbCrLf
GetRSFromSpwithParam "spGetwoSAOregonPursDate '" & dtmStartDate & "','" & dtmEndDate & "'", oRsDate
If Not oRsDate.EOF then
      While Not oRsDate.EOF
            'Use Date
            strDataXML = strDataXML & "<useDate>"
            strDataXML = strDataXML & oRsDate("dtmDateApplied") & "</useDate>" & VbCrLf
            strDataXML = strDataXML & "<UseLocation>" & VbCrLf
            strDataXML = strDataXML & "<UseLocaionWaterBasin>" & VbCrLf
            strDataXML = strDataXML & "<waterBasin>Willamette</waterBasin>" & VbCrLf
            strDataXML = strDataXML & "</UseLocationWaterBasin>" & VbCrLf
            strDataXML = strDataXML & "</UseLocation>" & VbCrLf
            'Get Product Detail
            GetRSFromSpwithParam "spGetwoSAOregonPURS '" & dtmStartDate & "','" & dtmEndDate & "'",oRsProducts
                  strDataXML = strDataXML & "<products>" & VbCrLf
                  While Not oRsProducts.EOF
                        If oRsDate("dtmDateApplied") = oRsProducts("dtmDateApplied") Then
                              strDataXML= strDataXML & "<UseReportProduct>" & VbCrLf
                              strDataXML= strDataXML & "<epaProductNumber>" & oRsProducts("strEPARegNumber").Value & "</epaProductNumber>" & VbCrLf
                              strDataXML= strDataXML & "<productName>" & oRsProducts("strChemicalProductName").Value & "</productName>" & VbCrLf
                              strDataXML = strDataXML & "<purpose>" & oRsProducts("strTargetTypeAltName").Value & "</purpose>" & VbCrLf
                              strDataXML = strDataXML & "<quantity>" & oRsProducts("dblTotalAmount").Value & "</quantity>" & VbCrLf
                              strDataXML = strDataXML & "<quantityUnit>" & oRsProducts("strUnitAltName").Value & "</quantityUnit>" & VbCrLf
                              strDataXML = strDataXML & "</UseReportProduct>" & VbCrLf
                        End If
                        oRsProducts.MoveNext()
                  Wend
                  oRsProducts.MoveFirst()
                  oRsDate.MoveNext()
      Wend
End If
      strDataXML = strDataXML & "</products>" & VbCrLf

'Concatenate & lose the XML document
strXML = strXML & strDataXML & "</UseReport>" & VbCrLf & "</Submission>"
'Output it
Response.Write(strXML)
'Destroy objects
Set oRsDate = nothing
Set oRsProducts = nothing
%>

***
End Code
***

0
 

Author Comment

by:krisred
ID: 17805346
Hi,
just had a another question.

Now how create a xml file (force download) with the above code

Thanks,

Kris
0
 

Author Comment

by:krisred
ID: 17805888

Thank you, I figured it out.

I just added the following to output

'Output it
Response.Buffer = True
Response.AddHeader "Content-type","text/xml"
Response.AddHeader "Content-Disposition", "attachment; filename=PURS_" & oXmlDate & "_" & oXmlStartDate & "_" & oXmlEndDate & ".xml"
Response.Write(strXML)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question