• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2128
  • Last Modified:

How to use DTS to export an XML file for importing into SAS

I'm using SQL Server 2000 and need a scheduled DTS job to export data from a query to an xml file for importing into SAS. The SAS documentation states that the XML file must adhere to the following sample structure:

What is the Required Physical Structure?
For an XML document to be successfully imported, the requirements for well-formed XML must translate to SAS as follows:
The root enclosing element (top-level node) of an XML document is the document container. For SAS, it translates to a library.

The nested elements occurring within the container (repeating element instances) begin with the second-level instance tag.

The repeating element instances must represent a rectangular organization. For a SAS data set, they become a collection of rows with a constant set of columns.
Here's an example of an XML document that illustrates the physical structure that SAS requires:

<?xml version="1.0" ?>
<LIBRARY>  -- root enclosing element
   <STUDENTS>  -- repeating element instance...one row
      <ID> 0755 </ID>
      <NAME> Brad Martin </NAME>
      <ADDRESS> 1611 Glengreen </ADDRESS>
      <CITY> Huntsville </CITY>
      <STATE> Texas </STATE>
   </STUDENTS>

   <STUDENTS>  -- repeating element instance...second row
      <ID> 1522 </ID>
      <NAME> Zac Harvell </NAME>
      <ADDRESS> 11900 Glenda </ADDRESS>
      <CITY> Houston </CITY>
      <STATE> Texas </STATE>
   </STUDENTS>
.
.  more instances of <STUDENTS>
.
</LIBRARY>

This is what happens when SAS imports the above XML document:

SAS goes to the second-level instance tag, which is <STUDENTS>, translates it as the data set name, and begins scanning the elements that are nested (contained) between the beginning <STUDENTS> and ending </STUDENTS> instance tags, looking for columns.

Because the instance tags <ID>, <NAME>, <ADDRESS>, <CITY>, and <STATE> are all contained within the <STUDENTS> beginning and ending instance tags, SAS interprets them as columns. The individual instance tag names become the data set column names. The repeating element instances are translated into a collection of rows with that constant set of columns.

How can I get DTS to export an XML file in this format including the <?xml version="1.0" ?> record?
0
rmk
Asked:
rmk
  • 4
  • 3
  • 2
1 Solution
 
arbertCommented:
You can use the FOR XML clause on a query, but I think the output that gets generated is a little more "complex" than SAS wants.  Have you tried that?
0
 
rmkAuthor Commented:
Yes, I've tried the "for xml" clause. It provides most but not all of what I want. The big problem is that I can't find a way to use it in DTS where exporting to a text file does not work with xml
0
 
arbertCommented:
So don't use DTS--use OSQL or BCP and output it to a file.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
rmkAuthor Commented:
I've successfully exported a small file with sp_makewebtask, but I get a badly formatted file for large amounts of data. I've successfully exported a small file with bcp, but I get file size errors for large amounts of data. I've successfully exported small and large files using the following active x script in DTS:

Function Main()

    Dim oCmd, sSQL, oDom

    ''' If MSXML 4.0 is not installed this will not work!
    Set oDom = CreateObject("Msxml2.DOMDocument.4.0")


    Set oCmd = CreateObject("ADODB.Command")
    oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=mydatabase;UID=myid;Password=mypassword"

    sSQL = "<?xml version=""1.0"" ?><ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & _
           "select * from myview for xml auto,elements</sql:query></ROOT>"

    oCmd.CommandText = sSQL
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"

    oCmd.Properties("Output Stream") = oDom
    oCmd.Execute , , 1024

    oDom.Save "c:\temp\myfile.xml"

    Main = DTSTaskExecResult_Success

End Function
0
 
Anthony PerkinsCommented:
Instead of using XML AUTO, you can use XML EXPLICIT to get the exact format you need.
0
 
Anthony PerkinsCommented:
And you do not need to instantiate an object as Msxml2.DOMDocument.4.0, you can use the ADO Stream object instead.
0
 
rmkAuthor Commented:
To: acperkins - If I simply use the ADO Stream object, do I still need msxml 4.0 or can I get away with 3.0 or even 2.0? Since I've never used the ADO stream object before, can you provide the appropriate code for this xml export? Thx
0
 
Anthony PerkinsCommented:
>>do I still need msxml 4.0 or can I get away with 3.0 or even 2.0? <<
This depends on SQL Server.  I believe it uses one of the later SP form 3.0.  But you could find this info pretty easily on MSDN.

>>Since I've never used the ADO stream object before, can you provide the appropriate code for this xml export?<<
No different than you are currently using with XML (they are stream objects after all)

Try this (untested)
Function Main()

    Dim oCmd, sSQL, oStm

    Set oCmd = CreateObject("ADODB.Command")
    oCmd.ActiveConnection = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=mydatabase;UID=myid;Password=mypassword"

    sSQL = "<?xml version=""1.0"" ?><ROOT xmlns:sql=""urn:schemas-microsoft-com:xml-sql""><sql:query>" & _
           "select * from myview for xml auto,elements</sql:query></ROOT>"
    oCmd.CommandType = 1          ' adCmdText
    oCmd.CommandText = sSQL
    oCmd.Dialect = "{5D531CB2-E6Ed-11D2-B252-00C04F681B71}"
    Set oStm = CreateObject("ADODB.Stream")
    oStm.Open
    oCmd.Properties("Output Stream") = oStm
    oCmd.Execute , , 1024               ' adExecuteStream

    oStm.SaveToFile "c:\temp\myfile.xml"

    oStm.Close

    Set oStm = Nothing
    Set oCmd = Nothing

    Main = DTSTaskExecResult_Success

End Function
0
 
rmkAuthor Commented:
Awesome - thanks so much
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now