Advanced Search

Convert database queries to xml using asp

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

Expand | Embed | Plain Text

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.