SQL data to XML



Hi,
I am helping a customer move data into NetSuite.
My knowledge of XML is sketchy; it has improved from almost nothing since I
started on this project.

I have a query ready to extract data, which I have rewritten using sample
data from the adventureworks database.
(pated below)

How do I save the output of this query to a file?
How do I add a reference to the DTD?
<!DOCTYPE smbxml SYSTEM "http://www.netsuite.com/xml/dtd/smb_5_01.dtd";>

What I'm doing for now is that I run the query in SQL MAnagement Studio,
manually paste the xml header and DTD lines and save it to a file. Surely
there must be a few ways that are better?

Ideally, I'd like to use VBScript - I'd have a shortcut that I would
execute, vbscript prompts for output filename and that's that
(I'll be using integrated security)

I started reading about XSL trnsformations and I still havent grasped it
well enough to apply it to what I need to get done.
I know there is the XML DOM, but I know nothing about it either. Should I
look more into the XML DOM?

Any advice, sample code and pointers to resources would be helpful. Thanks
in advnace for the assistance.

Regards
Habib
------------sample query ------------------------
select (
select rtrim(C.CustomerID) as [@handle]
,(select
(select
CustomerID as [@handle]
, TerritoryID as [territory]
, AccountNumber as [accountNumber]
, CustomerType as [customerType]
, rowguid as [rowGuid]
, convert( char(10),ModifiedDate, 101) as [modifiedDate]
, (Select AddressID as [addressRef/name]
, AddressTypeID as [addressType/name]
, convert( char(10),ModifiedDate, 101) as [modifiedDate]
from sales.customerAddress
where CustomerID = C.CustomerID
for XML PATH('AddressLine'), TYPE ) as [AddressList]
for XML PATH ('customer'), type)
for XML PATH ('addOrUpdate'), type)
from sales.Customer C
where customerID < 5
for XML PATH ('request'), type)
for XML PATH ('smbXML'), type




.

Relevant Pages

  • RE: For XML problem
    ... DECLARE @sql NVARCHAR ... DECLARE @data XML; ... ROOT )'; ... where CustomerID Like 'A%' ...
    (microsoft.public.sqlserver.programming)
  • RE: For XML problem
    ... Also bear in mind XML is case-sensitive, hence I've changed your XPath to ... DECLARE @sql NVARCHAR ... Like ''A%'' FOR XML auto, ... where CustomerID Like 'A%' ...
    (microsoft.public.sqlserver.programming)
  • Re: For XML problem
    ... DECLARE @sql NVARCHAR ... DECLARE @data XML; ... ROOT )'; ... where CustomerID Like 'A%' ...
    (microsoft.public.sqlserver.programming)
  • Re: Annotated schema with views?
    ... I think using the XML PATH is going to do the trick for me. ... >> to construct one Xml document.instead of executing one complex query. ... >>> SQLXML and .NET Serializer reduces the ETL time significantly. ...
    (microsoft.public.sqlserver.xml)
  • FOR XML Path OutPut
    ... How do I get an output from the query below one ... can manipulate the output? ... select CustomerID as "@ID", ... FOR XML PATH ...
    (microsoft.public.sqlserver.programming)
Loading