views:

552

answers:

2

I want to convert below SQL Server code to VBScript in classic ASP...

DECLARE @idoc int
DECLARE @xdoc nvarchar(4000)
DECLARE @xmldoc xml
Set @xmldoc = '<Root><Authors au_id="409-56-7008" au_lname="Bennet" au_fname="Abraham"><Titles title="The Busy Executive&apos;s Database Guide"/></Authors><Authors au_id="648-92-1872" au_lname="Blotchet-Halls" au_fname="Reginald"><Titles title="Fifty Years in Buckingham Palace Kitchens"/></Authors><Authors au_id="238-95-7766" au_lname="Carson" au_fname="Cheryl"><Titles title="But Is It User Friendly"/></Authors><Authors au_id="722-51-5454" au_lname="DeFrance" au_fname="Michel"><Titles title="The Gourmet Microwave"/></Authors><Authors au_id="712-45-1867" au_lname="del Castillo" au_fname="Innes"><Titles title="Silicon Valley Gastronomic Treats"/></Authors><Authors au_id="427-17-2319" au_lname="Dull" au_fname="Ann"><Titles title="Secrets of Silicon Valley"/></Authors><Authors au_id="267-41-2394" au_lname="Ellis" au_fname="Michael"><Titles title="Cooking with Computers: Surreptitious Balance Sheets"/><Titles title="Sushi for Anyone"/></Authors><Authors au_id="213-46-8915" au_lname="Green" au_fname="Marjorie"><Titles title="The Busy Executive&apos;s Database Guide"/><Titles title="You Can Combat Computer Stress"/></Authors></Root>' 

SELECT 
 Authors.value('./@au_id', 'varchar(20)') as au_id,
 Authors.value('./@au_fname', 'varchar(20)') as au_fname,
 Authors.value('./@au_lname', 'varchar(20)') as au_lname,
 Authors.value('Titles[1]/@title', 'varchar(20)') as Title
FROM 
@xmldoc.nodes('/Root/Authors') as TableValues(Authors)

How write the above code in classic ASP (VBScript)?

Can you help me with this?

+3  A: 

I would suggest encapsulating this logic into a stored procedure. Then simply call this sproc from your classic asp page. You'll be spending far too much time converting this logic from SQL Server's built in XML parsing capabilities to VBScript. VBScript doesn't have a rich set of functionality to parse xml documents.

Use the best tool for the job. If there is a management constraint, perhaps try to appeal to someone's sense of sanity. I know this doesn't answer your specific question as you might have expected.

 Create Procedure GetAuthors

AS 
    DECLARE @idoc int ,
            @xdoc nvarchar(4000) ,
            @xmldoc xml 

    SELECT @xmldoc = ''


    SELECT Authors.value('./@au_id', 'varchar(20)') as au_id, 
    Authors.value('./@au_fname', 'varchar(20)') as au_fname, 
    Authors.value('./@au_lname', 'varchar(20)') as au_lname, 
    Authors.value('Titles[1]/@title', 'varchar(20)') as Title 
    FROM @xmldoc.nodes('/Root/Authors') as TableValues(Authors)
p.campbell
+3  A: 
<%

Dim xmldoc
Set xmldoc = Server.CreateObject("MSXML2.DOMDocument.4.0")

xmldoc.SetProperty "SelectionLanguage", "XPath"
''# note the escaped double quotes
xmldoc.LoadXml "<Root><Authors au_id=""409-56-7008"" au_lname=""Bennet"" au_fname=""Abraham""><Titles title=""The Busy Executive&apos;s Database Guide""/></Authors><Authors au_id=""648-92-1872"" au_lname=""Blotchet-Halls"" au_fname=""Reginald""><Titles title=""Fifty Years in Buckingham Palace Kitchens""/></Authors><Authors au_id=""238-95-7766"" au_lname=""Carson"" au_fname=""Cheryl""><Titles title=""But Is It User Friendly""/></Authors><Authors au_id=""722-51-5454"" au_lname=""DeFrance"" au_fname=""Michel""><Titles title=""The Gourmet Microwave""/></Authors><Authors au_id=""712-45-1867"" au_lname=""del Castillo"" au_fname=""Innes""><Titles title=""Silicon Valley Gastronomic Treats""/></Authors><Authors au_id=""427-17-2319"" au_lname=""Dull"" au_fname=""Ann""><Titles title=""Secrets of Silicon Valley""/></Authors><Authors au_id=""267-41-2394"" au_lname=""Ellis"" au_fname=""Michael""><Titles title=""Cooking with Computers: Surreptitious Balance Sheets""/><Titles title=""Sushi for Anyone""/></Authors><Authors au_id=""213-46-8915"" au_lname=""Green"" au_fname=""Marjorie""><Titles title=""The Busy Executive&apos;s Database Guide""/><Titles title=""You Can Combat Computer Stress""/></Authors></Root>"

Dim Author

Response.Write "<table>" & vbNewLine
For Each Author In xmldoc.SelectNodes("/Root/Authors")
  Response.Write "<tr>" & vbNewLine
  WriteTableCell Author.GetAttribute("au_id")
  WriteTableCell Author.GetAttribute("au_fname")
  WriteTableCell Author.GetAttribute("au_lname")
  WriteTableCell Author.SelectSingleNode("Titles[1]/@title")
  Response.Write "</tr>" & vbNewLine
Next
Response.Write "</table>" & vbNewLine

''# table cell output factored into a Sub, for reuse and cleaner code
Sub WriteTableCell(xmldata)
  Dim s

  If IsObject(xmldata) Then
    If xmldata Is Nothing Then
      s = ""
    Else
      s = xmldata.text
    End If
  Else
    s = CStr(xmldata)
  End If

  Response.Write "<td>" & Server.HTMLEncode(s) & "</td>" & vbNewLine
End Sub

%>

Tested, works.

Tomalak
great job Tomalak!
p.campbell
+1. Nicely done. A couple of suggestions. Use MSXML3.DOMDocument.3.0 (I've come across machines that have had their registries munged by a bad install of MSXML2 causing the unqualified ProgID to return an MSXML2 implementation even with MSXML3 installed). Second s = "" causes the TD not to render, a no-break space ought to do the trick s = ChrW(8239)
AnthonyWJones
To my knowledge, there is no such thing as "MSXML3". Every version up to 6.0 uses "MSXML2" in the ProgId. (http://msdn.microsoft.com/en-gb/library/ms764622.aspx). I take it that this is just a typo on your part. :-) I'll change it to use 4.0 which should be widely deployed and available, also it's more than enough for the job at hand. Regarding the 's = ""' - the cell is just not displayed, which is easily fixed with CSS and 'empty-cells: show;' in a reasonably modern browser. Also, the portable nbsp is Chr(160), IMHO.
Tomalak
Oops yes a type. MSXML2.DOMDocument.3.0. I would also avoid 4.0 for two reasons a.) whilst it is widely deployed it isn't guaranteed to be present on all currently supported windows servers b.) MS are only actively maintaining the 3.0 (because its by far the most widely used) and 6.0 (because its the current and last in the line of COM XML) dlls.
AnthonyWJones
Yes 160 is better choice I couldn't remember if that was a 1252 specific code point or a unicode one. empty-cells only gets support in IE in the IE8 so may not be a good choice for a public site right now.
AnthonyWJones