Solved

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

Posted on 2004-04-29
9
2,098 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

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.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

813 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

18 Experts available now in Live!

Get 1:1 Help Now