SQL Server 2005 XML with Winbatch

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

ADO DAO

	ADSI LDAP CDO
	Samples from Users

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

SQL Server 2005 XML with Winbatch

 Keywords: SQL Server ADO ADODB DOM XML

The attached zip contains a PDF and 2 scripts. The PDF is a tutorial, of sorts, for understaning how Winbatch can interact with SQL Server to obtain XML data. 1 script can generate the xml/xsd files referenced in the PDF and the other was contributed by Jenny Palonus to illustrate using then DOM Extender with those output files. [ Winbatch_SQLServer2005_xml.zip ]

;Winbatch 2007C - testing SQLXMLOLEDB query, and SQL Native Client
;                 w/ SQL SERVER EXPRESS 2005
;
;This script illustrates 4 methods to query an SQL Server Table
;and and return results as XML, with option to load into Excel
;
;NOTE: This script uses a simple Dialog with no callback
;      each query is self-contained in its own subroutine
;      with an errorhandler
;
;Stan Littlefield, September 23, 2007
;////////////////////////////////////////////////////////////////////////

GoSub udfs
IntControl(73,1,0,0,0)
cTemplate = DirScript():"emp_template.xml"
cXSD = DirScript():"emp_xsd.xml"
cXML = DirScript():"employee_xml.xml"
cProv = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;"
cProv1 = "Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;"
cProv2 = "Provider=SQLNCLI.1.1;Integrated Security=SSPI;Persist Security Info=False;"
cSrc = "Data Source=.\SQLEXPRESS;Initial File Name="
cData = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
adExecuteStream=1024

cINI = DirScript():"sqlxml.ini"
If ! FileExist(cINI)
   try=1
   q1=0
   q2=0
   q3=0
   q4=0
Else
   try=IniReadPvt("Main","try","0",cINI)
   q1=IniReadPvt("Main","q1","0",cINI)
   q2=IniReadPvt("Main","q2","0",cINI)
   q3=IniReadPvt("Main","q3","0",cINI)
   q4=IniReadPvt("Main","q3","0",cINI)
EndIf

:start
If ! try Then qApp("Cannot Run Script:Components Missing")
oConn=0
oCmd=0
oS=0
oS1=0
XMLFormat=`WWWDLGED,6.1`

XMLCaption=`Winbatch SQL Server 2005 Express XML Query`
XMLX=028
XMLY=082
XMLWidth=192
XMLHeight=087
XMLNumControls=006
XMLProcedure=`DEFAULT`
XMLFont=`Tahoma|6144|70|34`
XMLTextColor=`0|0|0`
XMLBackground=`DEFAULT,0|0|255`
XMLConfig=0

XML001=`007,011,074,012,PUSHBUTTON,DEFAULT,"Test For Resources",1,1,0,"Tahoma|6144|70|34","255|255|0","0|0|255"`

If q1
   XML002=`097,011,074,012,PUSHBUTTON,DEFAULT,"SQLXML Query",2,2,0,"Tahoma|6144|70|34","255|255|0","0|0|255"`
Else
   XML002=`097,011,074,012,PUSHBUTTON,DEFAULT,"SQLXML Query",2,2,2,"Tahoma|6144|70|34","255|255|0","0|0|255"`
EndIf
If q2
   XML003=`007,037,074,012,PUSHBUTTON,DEFAULT,"Template Query",3,3,0,"Tahoma|6144|70|34","255|255|0","0|0|255"`
Else
   XML003=`007,037,074,012,PUSHBUTTON,DEFAULT,"Template Query",3,3,2,"Tahoma|6144|70|34","255|255|0","0|0|255"`
EndIf
If q3
   XML004=`097,037,074,012,PUSHBUTTON,DEFAULT,"XSD Query",4,4,0,"Tahoma|6144|70|34","255|255|0","0|0|255"`
Else
   XML004=`097,037,074,012,PUSHBUTTON,DEFAULT,"XSD Query",4,4,2,"Tahoma|6144|70|34","255|255|0","0|0|255"`
EndIf
If q4
   XML005=`007,063,074,012,PUSHBUTTON,DEFAULT,"Native Client",5,5,0,"Tahoma|6144|70|34","255|255|0","0|0|255"`
Else
   XML005=`007,063,074,012,PUSHBUTTON,DEFAULT,"Native Client",5,5,2,"Tahoma|6144|70|34","255|255|0","0|0|255"`
EndIf
XML006=`099,063,072,012,PUSHBUTTON,DEFAULT,"Cancel",6,6,32,"Tahoma|6144|70|34","255|255|0","255|0|0"`

BP=Dialog("XML")
Select BP
   Case 1
      chk()
      Break
   Case 2
      query1()
      Break
   Case 3
      query2()
      Break
   Case 4
      query3()
      Break
   Case 5
      query4()
      Break
   Case 6
      Exit
EndSelect
Goto start

:WBERRORHANDLER
ErrorProcessing(1,1,0,0)
Exit

;///////////////////////////////////////////////////////////////////////////////////////
:udfs
#DefineSubRoutine query1()
IntControl(73,1,0,0,0)
BoxOpen("Please Wait","Processing XML Query")
If FileExist(cXML) Then FileDelete(cXML)
cConn = cProv1:csrc:cData
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
oS = CreateObject("ADODB.Stream")
oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeOut=120
oCmd.ActiveConnection = oConn
oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
oCmd.Properties("ClientSideXML") = @TRUE
cSQL ="<ROOT xmlns:sql='urn:schemas-microsoft-com:xml-sql'> "
cSQL=cSQL:" <sql:query> "
cSQL=cSQL:" SELECT * FROM [HumanResources].[Employee] FOR XML AUTO "
;cSQL=cSQL:" SELECT * FROM [HumanResources].[Employee] FOR XML AUTO, ELEMENTS "
cSQL=cSQL:" </sql:query> "
cSQL=cSQL:" </ROOT> "
oCmd.CommandText = cSQL
oS.Open()
oCmd.Properties("Output Stream").Value = oS
oCmd.Properties("Output Encoding").Value = "utf-8"
oCmd.Execute( , , adExecuteStream)
oS.Position = 0
;//////////////////////////////////////////////
cVar = StrReplace(oS.ReadText(),">",">":@CRLF)
oS.Position = 0
oS.WriteText(cVar)
oS.SaveToFile(cXML,2)
;//////////////////////////////////////////////
oS.Close()
oS=0
oCmd=0
oConn.Close()
oConn=0
BoxShut()
If FileExist(cXML)
   If AskYesNo("XML File Created From Query ":cXML, 'Load XML File Into Excel') Then loadxl()
EndIf
Return(1)

:CANCEL
Return(1)
:WBERRORHANDLER
If (IsObject(oS) && oS.State<>0) Then oS.Close()
oS=0
oCmd=0
If (IsObject(oConn) && oConn.State<>0) Then oConn.Close()
oConn=0
BoxShut()
ErrorProcessing(1,1,0,0)
IntControl(73,1,0,0,0)
Return(0)
#EndSubRoutine

#DefineSubRoutine query2()
IntControl(73,1,0,0,0)
BoxOpen("Please Wait","Processing XML Query")
If FileExist(cXML) Then FileDelete(cXML)
cConn = cProv:csrc:cData
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
oS = CreateObject("ADODB.Stream")
oS1 = CreateObject("ADODB.Stream")
oS1.Type=2
oS1.Charset = "ascii"
oS1.Open()
oS1.LoadFromFile(cTemplate)
oCmd = CreateObject("ADODB.Command")
oCmd.ActiveConnection = oConn
oCmd.Dialect="{C8B521FB-5CF3-11CE-ADE5-00AA0044773D}"
oCmd.CommandStream = oS1
oS.Open()
oCmd.Properties("Output Stream").Value = oS
oCmd.Properties("Output Encoding").Value = "utf-8"
oCmd.Execute( , , adExecuteStream)
oS1.Position = 0
;//////////////////////////////////////////////
cVar = StrReplace(oS.ReadText(),">",">":@CRLF)
oS.Position = 0
oS.WriteText(cVar)
oS.SaveToFile(cXML,2)
;//////////////////////////////////////////////
oS1.Close()
oS1=0
oS.Close()
oS=0
oCmd=0
oConn.Close()
oConn=0
BoxShut()
If FileExist(cXML)
   If AskYesNo("XML File Created From Query ":cXML, 'Load XML File Into Excel') Then loadxl()
EndIf
Return(1)

:CANCEL
Return(1)
:WBERRORHANDLER
If (IsObject(oS1) && oS1.State<>0) Then oS1.Close()
oS1=0
If (IsObject(oS) && oS.State<>0) Then oS.Close()
oS=0
oCmd=0
If (IsObject(oConn) && oConn.State<>0) Then oConn.Close()
oConn=0
BoxShut()
ErrorProcessing(1,1,0,0)
IntControl(73,1,0,0,0)
Return(0)
#EndSubRoutine

#DefineSubRoutine query3()
IntControl(73,1,0,0,0)
BoxOpen("Please Wait","Processing XML Query")
If FileExist(cXML) Then FileDelete(cXML)
cConn = cProv1:csrc:cData
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
oS = CreateObject("ADODB.Stream")
oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeOut=120
oCmd.ActiveConnection = oConn
oCmd.Dialect = "{EC2A4293-E898-11D2-B1B7-00C04F680C56}"
oCmd.Properties("ClientSideXML") = @TRUE
oCmd.CommandText = "root"
oCmd.Properties("Output Stream").Value = oS
oCmd.Properties("Mapping Schema").Value = cXSD
oS.Open()
oCmd.Properties("Output Stream").Value = oS
oCmd.Properties("Output Encoding").Value = "utf-8"
oCmd.Execute( , , adExecuteStream)
oS.Position = 0
;//////////////////////////////////////////////
cVar = StrReplace(oS.ReadText(),"<",@CRLF:"<")
oS.Position = 0
oS.WriteText(cVar)
oS.SaveToFile(cXML,2)
;//////////////////////////////////////////////
oS.Close()
oS=0
oCmd=0
oConn.Close()
oConn=0
BoxShut()
If FileExist(cXML)
   If AskYesNo("XML File Created From Query ":cXML, 'Load XML File Into Excel') Then loadxl()
EndIf
Return(1)

:CANCEL
Return(1)
:WBERRORHANDLER
If (IsObject(oS) && oS.State<>0) Then oS.Close()
oS=0
oCmd=0
If (IsObject(oConn) && oConn.State<>0) Then oConn.Close()
oConn=0
BoxShut()
ErrorProcessing(1,1,0,0)
IntControl(73,1,0,0,0)
Return(0)
#EndSubRoutine

#DefineSubRoutine query4()
IntControl(73,1,0,0,0)
BoxOpen("Please Wait","Processing XML Query")
If FileExist(cXML) Then FileDelete(cXML)
cConn = cProv2:csrc:cData
oConn = CreateObject("ADODB.Connection")
oConn.Open(cConn)
oS = CreateObject("ADODB.Stream")
oCmd = CreateObject("ADODB.Command")
oCmd.CommandTimeOut=120
oCmd.ActiveConnection = oConn
cSQL="SELECT * FROM [HumanResources].[Employee] AS Employee FOR XML AUTO"
oCmd.CommandText = cSQL
oS.Open()
oCmd.Properties("xml root") = "root"
oCmd.Properties("Output Stream").Value = oS
oCmd.Properties("Output Encoding").Value = "utf-8"
oCmd.Execute( , , adExecuteStream)
oS.Position = 0
;//////////////////////////////////////////////
cVar = StrReplace(oS.ReadText(),">",">":@CRLF)
oS.Position = 0
oS.WriteText(cVar)
oS.SaveToFile(cXML,2)
;//////////////////////////////////////////////
oS.Close()
oS=0
oCmd=0
oConn.Close()
oConn=0
BoxShut()
If FileExist(cXML)
   If AskYesNo("XML File Created From Query ":cXML, 'Load XML File Into Excel') Then loadxl()
EndIf
Return(1)

:CANCEL
Return(1)
:WBERRORHANDLER
If (IsObject(oS) && oS.State<>0) Then oS.Close()
oS=0
oCmd=0
If (IsObject(oConn) && oConn.State<>0) Then oConn.Close()
oConn=0
BoxShut()
ErrorProcessing(1,1,0,0)
IntControl(73,1,0,0,0)
Return(0)
#EndSubRoutine


#DefineSubRoutine loadxl()
IntControl(73,1,0,0,0)
oXL=0
oXL = CreateObject("Excel.Application")
If oXL.Version<11
   Display(2,"Cannot Continue","Script Requires Office 2003 Excel Version")
   oXL=0
   Return(0)
EndIf
oXL.Visible          = 1
oXL.ScreenUpdating   = 1
oXL.UserControl      = 1
oXL.DisplayAlerts    = 0
xlXmlLoadImportToList = 2
xlXmlLoadMapXml = 3
xlXmlLoadOpenXml = 1
xlXmlLoadPromptUser = 0
oXL.WorkBooks.OpenXML(::Filename="%cXML%",LoadOption=xlXmlLoadImportToList)
oXL=0
Return(1)
:WBERRORHANDLER
If IsObject(oXL) Then oXL.Quit()
oXL=0
ErrorProcessing(1,1,0,0)
IntControl(73,1,0,0,0)
Return(0)
#EndSubRoutine

#DefineSubRoutine chk()
If IsInReg("SQLOLEDB.1") && IsInReg("SQLXMLOLEDB.3.0")
   try=1
Else
   try=0
EndIf
IniWritePvt("Main","try",1,cINI)

If ! FileExist(cData)
   try=0
   IniWritePvt("Main","try",0,cINI)
EndIf
q1=1
IniWritePvt("Main","q1",1,cINI)


If ! try
   q1=0
   q2=0
   q3=0
   q4=0
   IniWritePvt("Main","q1",0,cINI)
   IniWritePvt("Main","q2",0,cINI)
   IniWritePvt("Main","q3",0,cINI)
   IniWritePvt("Main","q4",0,cINI)
Else
   q1=1
   q2=1
   q3=1
   q4=1
   IniWritePvt("Main","q1",1,cINI)
   IniWritePvt("Main","q2",1,cINI)
   IniWritePvt("Main","q3",1,cINI)
   IniWritePvt("Main","q4",1,cINI)
   If ! FileExist(cTemplate)
      q2=0
      IniWritePvt("Main","q2",0,cINI)
   EndIf
   If ! FileExist(cXSD)
      q3=0
      IniWritePvt("Main","q3",0,cINI)
   EndIf
   If ! IsInReg("SQLNCLI.1")
      q4=0
      IniWritePvt("Main","q4",0,cINI)
   EndIf
EndIf
Display(3,"INI Settings",FileGet(cINI))
Return(1)
#EndSubRoutine


#DefineFunction isObject(obj)
Return(VarType(obj)>=1024)
#EndFunction

#DefineFunction isInReg(cProg)
Return( RegExistKey(@REGCLASSES,cProg) )
#EndFunction

#DefineFunction qApp(cProg)
EXFormat=`WWWDLGED,6.1`

EXCaption=`Program is Being Terminated...`
EXX=-01
EXY=-01
EXWidth=238
EXHeight=045
EXNumControls=003
EXProcedure=`DEFAULT`
EXFont=`Microsoft Sans Serif|6144|70|34`
EXTextColor=`255|255|0`
EXBackground=`DEFAULT,0|0|255`
EXConfig=0

EX001=`197,003,036,036,PUSHBUTTON,DEFAULT,"Quit",1,1,32,"Microsoft Sans Serif|6144|70|34","0|0|255","255|255|255"`
EX002=`005,003,188,012,STATICTEXT,DEFAULT,"The script cannot continue on this PC because",DEFAULT,2,512,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|255"`
EX003=`005,019,188,020,VARYTEXT,cProg,DEFAULT,DEFAULT,4,512,"Microsoft Sans Serif|6144|70|34","255|255|0","0|0|255"`

ButtonPushed=Dialog("EX")
Exit
#EndFunction

#DefineSubRoutine ErrorProcessing(deleteIni,showerr,logfile,Err_Array)
If VarType(Err_Array) ==256
   WbError = Err_Array[0]
   wberrorhandlerline = Err_Array[1]
   wberrorhandleroffset = Err_Array[2]
   wberrorhandlerassignment = Err_Array[3]
   wberrorhandlerfile = Err_Array[4]
   wberrortextstring = Err_Array[5]
   wberroradditionalinfo = Err_Array[6]
   wberrorinsegment = Err_Array[7]
Else
   WbError = LastError()
EndIf
WbTextcode = WbError
If WbError==1668||WbError==2669||WbError==3670
   ; 1668 ; "Minor user-defined error"
   ; 2669 ; "Moderate user-defined error"
   ; 3670 ; "Severe user-defined error"
   WbError = ItemExtract(1,IntControl(34,-1,0,0,0),":")
   WbTextcode = -1
EndIf
WbErrorString = IntControl(34,WbTextcode,0,0,0)
WbErrorDateTime = TimeYmdHms()
WbErrorFile = StrCat(DirWindows(0),"WWWBATCH.INI")
If deleteIni
   FileDelete(WbErrorFile)
   IniWritePvt(WbErrorDateTime,"CurrentScript",WbErrorHandlerFile      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorValue"   ,WbError                 ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ErrorString"  ,WbErrorString           ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptLine"   ,WbErrorHandlerLine      ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"ScriptOffset" ,WbErrorHandlerOffset    ,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarAssignment",WbErrorHandlerAssignment,WbErrorFile)
   IniWritePvt(WbErrorDateTime,"VarInSegment" ,WbErrorInSegment,WbErrorFile)
   IniWritePvt("","","",WbErrorFile)
EndIf
WbErrorMsgText = StrCat(WbErrorDateTime,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Current Script: ",WbErrorHandlerFile,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error# [",WbError,"]",@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"Error Text: ",wberrortextstring,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"[Extended Information] ",wberroradditionalinfo,@CRLF,@CRLF)
WbErrorMsgText = StrCat(WbErrorMsgText,"On Line:",@CRLF,WbErrorHandlerLine,@CRLF)
;WbErrorMsgText = StrCat(WbErrorMsgText,"Offset: ",WbErrorHandlerOffset,@CRLF)
If (WbErrorHandlerAssignment>"") Then %WbErrorHandlerAssignment% = "UNKNOWN"
WbErrorMsgText = StrCat(WbErrorMsgText,"Assignment/Variable: ",WbErrorHandlerAssignment,@CRLF)
If (WbErrorInSegment>"") Then WbErrorMsgText = StrCat(WbErrorMsgText,"In UDF/UDS: ",WbErrorInSegment,@CRLF)
If logfile
   cSep = StrCat(StrFill("=",50),@CRLF)
   cLogFile = StrCat(DirScript(),"log.err")
   If ! FileExist(cLogFile) Then FilePut(cLogFile,StrCat("Error Log",@CRLF,cSep))
   FilePut(cLogFile,StrCat(FileGet(cLogFile),WbErrorMsgText,cSep))
   Display(2,"An Error Occured",StrCat("written to ",cLogFile))
Else
   If showerr
      WbErrorMsgText = StrCat(WbErrorMsgText,"[THIS ERROR NOT WRITTEN TO LOG FILE]",@CRLF)
      Message("An Error Was Encountered",WbErrorMsgText)
   EndIf
EndIf
Return(1)
#EndSubRoutine

Return
;///////////////////////////////////////////////////////////////////////////////////////

;*****************************************************************************
; SOAP request/response tester
;
; This is a testbed for building up SOAP/XML-RPC/WSDL/UDDI helper functions, etc.
;*****************************************************************************
AddExtender ("WXDOM44i.dll")


;*****************************************************************************
; Main code.
;*****************************************************************************
sTitle = "Test of SQL->XML"
While (@TRUE)
   sXMLFile = AskFilename (sTitle : ": Choose data file or CANCEL", ".", "All Files|*.*|XML files|*.xml", "*.xml", 1)
   hXMLDoc = domParseFile (sXMLFile)

   hXSDDoc = 0
   bSchemaFile = AskYesNo (sTitle, "Is there a separate schema file?")
   If (bSchemaFile)
      sXSDFile = AskFilename (sTitle : ": Choose schema file", ".", "All Files|*.*|XSD files|*.xsd", "*.xsd", 1)
      hXSDDoc = domParseFile (sXSDFile)
      hXSDSchema = domGet1stChild(hXSDDoc, "*:schema") ; <xsd:schema> is the XSD file's root element
      If (!hXSDSchema)
         Message (sTitle, "Couldn't find a schema element in the XSD file!")
         Continue
      EndIf
   Else
      hXSDSchema = domGet1stChild(hXMLDoc:"\root", "*:schema") ; <xsd:schema> is a child of <root> in the XML file
      If (!hXSDSchema)
         Message (sTitle, "Couldn't find a schema element in the data file!")
         Continue
      EndIf
   EndIf

   ; The schema & its namespaces...
   sXSD = nsGetElementPrefix(hXSDSchema) ; normally would be "xsd"
   xsd_attribute   = sXSD : ":attribute"
   xsd_complexType = sXSD : ":complexType"
   xsd_element     = sXSD : ":element"
   xsd_maxLength   = sXSD : ":maxLength"
   xsd_restriction = sXSD : ":restriction"
   xsd_sequence    = sXSD : ":sequence"
   xsd_simpleType  = sXSD : ":simpleType"
   xsd_string      = sXSD : ":string"

   ; Figure out what kind of database generated our data...
   sDataSource = "unknown"
   If (nsGetPrefixFromURI(hXSDSchema,"http://schemas.microsoft.com/sqlserver/2004/sqltypes") != "")
      sDataSource = "SQLServer"
   Else
      If (nsGetPrefixFromURI(hXSDSchema,"urn:schemas-microsoft-com:officedata") != "")
         sDataSource = "Access"
         EndIf
   EndIf

   dataroot = ""
   sTablename = ""
   If (sDataSource == "Access")
      ; Figure out what the dataroot and the data table are called...
      sElemList = domChildElementItemize(hXSDSchema,xsd_element)
      For nElem=1 To ItemCount(sElemList,@TAB)
         hElem = ItemExtract (nElem, sElemList, @TAB)
         sTablename = domGetAttr(hElem:"\":xsd_complexType:"\":xsd_sequence:"\":xsd_element, "ref")
         If (sTablename!="")
            ; This element declares the data table. Its name attribute is the name of the dataroot itself...
            dataroot = "root\" : domGetAttr(hElem, "name")
            Break
         EndIf
      Next nElem
   Else
   If (sDataSource == "SQLServer")
      ; Figure out what the dataroot and the data table are called...
      hTableDef = domGet1stChild(hXSDSchema,xsd_element)
      If (hTableDef)
         sTablename = domGetAttr(hTableDef,"name")
         sTablename = ItemExtract(2,sTablename,".") ; The part after the dot is the tablename
      EndIf
      dataroot = "root"
   EndIf
   EndIf

   If (sTablename=="")
      Message (sTitle, "Couldn't find the data table in the schema!")
      Continue
   EndIf
   If (dataroot=="")
      Message (sTitle, "Couldn't determine the data root element's name from the schema!")
      Continue
   EndIf

   ; Get the schema definitions for the column names/types/lengths...
   If (sDataSource == "Access")
      sTopList = domGetElementsByTagname (hXSDSchema, xsd_element)
      For nTable=1 To ItemCount(sTopList,@TAB)
         hTable = ItemExtract(nTable,sTopList,@TAB)
         sName = domGetAttr(hTable,"name")
         If (sName==sTableName)
            ; This is our table. Now iterate the column definitions...
            sColList = domChildElementItemize (hTable:"\":xsd_complexType:"\":xsd_sequence, xsd_element)
            aColumns = ArrDimension (ItemCount(sColList,@TAB), 4) ; cols=name,jettype,sqltype,maxlen
            ArrInitialize (aColumns, "")
            For nCol=0 To ItemCount(sColList,@TAB)-1
               hCol = ItemExtract(nCol+1,sColList,@TAB)
               aColumns[nCol,0] = domGetAttr(hCol, "name")
               aColumns[nCol,1] = domGetAttr(hCol, "od:jetType")
               aColumns[nCol,2] = domGetAttr(hCol, "od:sqlSType")
               hRest = domGetNode (hCol:"\":xsd_simpleType:"\":xsd_restriction)
               If (hRest && domGetAttr(hRest,"base")==xsd_string)
                  aColumns[nCol,3] = domGetAttr(domGetNode(hRest:"\":xsd_maxLength), "value")
               EndIf
Message (sTitle, "col     =":aColumns[nCol,0]:@CRLF:"jetType =":aColumns[nCol,1]:@CRLF:"sqlSType=":aColumns[nCol,2]:@CRLF:"maxLen   =":aColumns[nCol,3])
            Next nCol
            Break
         EndIf
      Next nTable
   Else
   If (sDataSource == "SQLServer")
      hTableDef = domGet1stChild (hXSDSchema, xsd_element)
      ; This is our table. Now iterate the column definitions...
      sColList = domChildElementItemize (hTableDef:"\":xsd_complexType, xsd_attribute)
      aColumns = ArrDimension (ItemCount(sColList,@TAB), 4) ; cols=name,jettype(unused),sqltype,maxlen(unused)
      ArrInitialize (aColumns, "")
      For nCol=0 To ItemCount(sColList,@TAB)-1
         hCol = ItemExtract(nCol+1,sColList,@TAB)
         aColumns[nCol,0] = domGetAttr(hCol, "name")
         aColumns[nCol,2] = domGetAttr(hCol, "type")
Message (sTitle, "col     =":aColumns[nCol,0]:@CRLF:"jetType =":aColumns[nCol,1]:@CRLF:"sqlSType=":aColumns[nCol,2]:@CRLF:"maxLen   =":aColumns[nCol,3])
      Next nCol
   EndIf
   EndIf

   ; Now iterate the rows and, uh, do something with them...
   sRowList = domChildElementItemize (hXMLDoc:"\":dataroot, sTablename)
   For nRow = 1 To ItemCount(sRowList,@TAB)
      hRow = ItemExtract (nRow, sRowList,@TAB) ; An individual data row
   Next nRow

   domClose (hXSDDoc)
   domClose (hXMLDoc)
EndWhile

Article ID:   W17442

File Created: 2019:08:14:09:21:42

Last Updated: 2019:08:14:09:21:42