Solved

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

Posted on 2004-04-29
9
2,101 Views
Last Modified: 2013-11-19
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
Comment
Question by:rmk
  • 4
  • 3
  • 2
9 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 10955669
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
 

Author Comment

by:rmk
ID: 10958911
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
 
LVL 34

Expert Comment

by:arbert
ID: 10960815
So don't use DTS--use OSQL or BCP and output it to a file.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

Author Comment

by:rmk
ID: 10963564
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10963608
Instead of using XML AUTO, you can use XML EXPLICIT to get the exact format you need.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10963622
And you do not need to instantiate an object as Msxml2.DOMDocument.4.0, you can use the ADO Stream object instead.
0
 

Author Comment

by:rmk
ID: 10988401
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 10992588
>>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
 

Author Comment

by:rmk
ID: 10995154
Awesome - thanks so much
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Styling your websites can become very complex. Here I'll show how SASS can help you better organize, maintain and reuse your CSS code.
SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question