Convert SQL code that uses XML/XPath to VBScript (classic ASP)

Question

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?

Answer 1

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)

Answer 2

<%

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.