Export SQL To XML

shillr · August 1st, 2006, 10:14 AM

I am trying to mirror the following XML structure via SQL

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE ocs-b2b-document-request SYSTEM "HTTP://www.ntorder-dev.com/ocs-b2b-document-request.dtd">
<ocs-b2b-document-request>
<header>
  <authentication-info>
   <username>username</username>
   <password>password</password>
  </authentication-info>
<document-info>
<document-uid>Mult_Order-09999-2006-07-27 10:47:40.062</document-uid>
<document-date>2006-07-27 10:47:40.062</document-date>
</document-info>
</header>
<body>
  <order-entry>
   <orderHeader>
    <memberOrderNumber>Test1</memberOrderNumber>
    <scheme>WAY</scheme>
    <store>Z12345</store>
    <orderDate>2006-07-27</orderDate>
   </orderHeader>
   <orderLine>
    <itemCode>41160</itemCode>
    <quantity>1</quantity>
   </orderLine>
  </order-entry>
  <order-entry>
   <orderHeader>
    <memberOrderNumber>Test2</memberOrderNumber>
    <scheme>WAY</scheme>
    <store>Z12346</store>
    <orderDate>2006-07-29</orderDate>
   </orderHeader>
   <orderLine>
    <itemCode>11235</itemCode>
    <quantity>1</quantity>
   </orderLine>
  </body>
</ocs-b2b-document-request>

I have created a universal table as part of my approach and the fields that are coming from the universal table are higlighted as bold.

Would appreciate any guidance to mirror the above and the best way to execute the extraction of the data e.g. via DTS.

Thanks