Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-04-29
9
Medium Priority
?
2,117 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 1000 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Without even knowing it, most of us are using web applications on a daily basis.  In fact, Gmail and Yahoo email, Twitter, Facebook, and eBay are used by most of us daily—and they are web applications. We generally confuse these web applications to…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…

610 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