Re: SQL data to XML
- From: "Michael Rys [MSFT]" <mrys@xxxxxxxxxxxxxxxxxxxx>
- Date: Tue, 11 Jul 2006 14:34:52 -0700
I assume that the XML looks like you want, except for the DTD?
You can use the ADO or ADO.net providers to get the XML back (see books
online for examples) and then save the XML programmatically into a file. If
you want to add the DOCTYPE, you add it to the file before you add the
returned XML.
HTH
Michael
"HSalim[MVP]" <HSalim@xxxxxxx> wrote in message
news:%23ZVU6k3oGHA.1796@xxxxxxxxxxxxxxxxxxxxxxx
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
.
- References:
- SQL data to XML
- From: HSalim[MVP]
- SQL data to XML
- Prev by Date: Re: Sql Server 2005 Xml Dml using .modify()
- Next by Date: Re: Invalid XML Elements found inside cync block
- Previous by thread: SQL data to XML
- Next by thread: Re: Sql Server 2005 Xml Dml using .modify()
- Index(es):
Relevant Pages
|
Loading
