Solved

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

Posted on 2004-04-29
9
2,090 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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…

708 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now