SQLServer XML Query

[stanl]

I'm sure this can be replicated since it uses Adventureworks (SQL Servers' answer to Northwind) and I referenced it in the default directory. Always been interested in Excel's LoadXML option- it won't take a persisted recordset, and it's fussy about other xml I tried. Anyway, the sub requires MDAC 2.6 or later [for the ADODB.Stream dialect property] and don't think it will work with <Office 2003. I made a comment about utf-8, as I muddled through a few errors and incompletes before I got it right. Most examples out there are for 4.0 and use Provider=SQLNCLI.1, but that doesn't come with the Express Edition

I think it's pretty cool Stan

 
Sub sqlxmlquery()
cXML = ActiveWorkbook.Path & "\employee_xml.xml"
If Dir(cXML) <> "" Then Kill (cXML)
adExecuteStream = 1024
oConn = 0
oCmd = 0
oS = 0
cConn = "Provider=SQLXMLOLEDB.3.0;Data Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=.\SQLEXPRESS;Initial File Name=C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
Set oConn = CreateObject("ADODB.Connection")
Set oS = CreateObject("ADODB.Stream")
Set oCmd = CreateObject("ADODB.Command")
oConn.Open cConn
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 & " </sql:query> "
cSQL = cSQL & " </ROOT> "
oCmd.CommandText = cSQL
oS.Open
oCmd.Properties("Output Stream").Value = oS
'you could try this instead of utf-8
'however, you will NOT be able to load the file into Excel
'as an XML file
'oCmd.Properties("Output Encoding").Value = "iso-8859-1"
oCmd.Properties("Output Encoding").Value = "utf-8"
oCmd.Execute , , adExecuteStream
oS.Position = 0
'if you do not add the crlf you will
'end up with a very very long single line
'it will not effect loading the file into Excel
'as XML
cTxt = Replace(oS.ReadText(), ">", ">" & vbCrLf)
oS.Position = 0
oS.WriteText cTxt
oS.SaveToFile cXML, 2
oS.Close
Set oS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing
If Dir(cXML) <> "" Then
MsgBox "XML Query Created " & cXML
Workbooks.OpenXML Filename:=cXML, LoadOption:=xlXmlLoadImportToList
End If
End Sub

[stanl]

If anyone, anyone at all, is interested there are 2 other methods to skin this cat (1) using an xml template file (2) using an xsd (XML Schema Definition File). With more data delivered via XML and a little more popularity for SQL Server 2005, I'd consider it worthwhile for skillset++

Stan

[stanl]

...