Convert database queries to xml using asp


/ Published in: ASP
Save to your folder(s)



Copy this code and paste it in your HTML
  1. ASP
  2.  
  3. Manual: This version loops over a query. Edit the Query and XML node names to match your needs.
  4.  
  5. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  6. <%
  7. Dim MM_conn_STRING
  8. MM_conn_STRING = "dsn=image_gallery;uid=xxxx;pwd=xxxx"
  9. %>
  10. <%
  11. Dim rsImages
  12. Dim rsImages_cmd
  13. Dim rsImages_numRows
  14.  
  15. ' Query the database and get all the records from the Images table
  16. Set rsImages_cmd = Server.CreateObject ("ADODB.Command")
  17. rsImages_cmd.ActiveConnection = MM_conn_STRING
  18. rsImages_cmd.CommandText = "SELECT ID, AlbumName, ImagePath, ImageDescription, UploadDate FROM images"
  19. rsImages_cmd.Prepared = true
  20.  
  21. Set rsImages = rsImages_cmd.Execute
  22.  
  23. ' Send the headers
  24. Response.ContentType = "text/xml"
  25. Response.AddHeader "Pragma", "public"
  26. Response.AddHeader "Cache-control", "private"
  27. Response.AddHeader "Expires", "-1"
  28. %><?xml version="1.0" encoding="utf-8"?>
  29. <images>
  30. <% While (NOT rsImages.EOF) %>
  31. <image>
  32. <ID><%=(rsImages.Fields.Item("ID").Value)%></ID>
  33. <album><![CDATA[<%=(rsImages.Fields.Item("AlbumName").Value)%>]]></album>
  34. <path><![CDATA[<%=(rsImages.Fields.Item("ImagePath").Value)%>]]></path>
  35. <description><![CDATA[<%=(rsImages.Fields.Item("ImageDescription").Value)%>]]></description>
  36. <date><![CDATA[<%=(rsImages.Fields.Item("UploadDate").Value)%>]]></date>
  37. </image>
  38. <%
  39. rsImages.MoveNext()
  40. Wend
  41. %>
  42. </images>
  43. <%
  44. rsImages.Close()
  45. Set rsImages = Nothing
  46. %>
  47.  
  48.  
  49. Automatic: This version evaluates the query and automatically builds the nodes from the column names.
  50.  
  51. <%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
  52. <%
  53. Dim MM_conn_STRING
  54. MM_conn_STRING = "dsn=image_gallery;uid=xxxx;pwd=xxxx"
  55. %>
  56. <%
  57. Dim rsAll
  58. Dim rsAll_cmd
  59. Dim rsAll_numRows
  60.  
  61. ' Query the database and get all the records from the Images table
  62. Set rsAll_cmd = Server.CreateObject ("ADODB.Command")
  63. rsAll_cmd.ActiveConnection = MM_conn_STRING
  64. rsAll_cmd.CommandText = "SELECT * FROM Images"
  65. rsAll_cmd.Prepared = true
  66.  
  67. Set rsAll = rsAll_cmd.Execute
  68.  
  69. ' Send the headers
  70. Response.ContentType = "text/xml"
  71. Response.AddHeader "Pragma", "public"
  72. Response.AddHeader "Cache-control", "private"
  73. Response.AddHeader "Expires", "-1"
  74. %><?xml version="1.0" encoding="utf-8"?>
  75. <root>
  76. <% While (NOT rsAll.EOF) %>
  77. <row>
  78. <%
  79. For each field in rsAll.Fields
  80. column = field.name
  81. %>
  82. <<%=column%>><![CDATA[<%=(rsAll.Fields.Item(column).Value)%>]]></<%=column%>>
  83. <%
  84. Next
  85. %>
  86. </row>
  87. <%
  88. rsAll.MoveNext()
  89. Wend
  90. %>
  91. </root>
  92. <%
  93. rsAll.Close()
  94. Set rsAll = Nothing
  95. %>

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.