|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Exporting to an XML filebut it doesn't format the file correctly Running this: sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml', @query = 'SELECT rtrim(cust_ID) AS "Role ID", rtrim(LASTNAME) AS LName, rtrim(FIRSTNAME) AS Fname FROM web_met for xml auto', @templatefile = 'c:\temp\template.tpl' Produces this: <?xml version="1.0"?> <root><web_met Role_x0020_ID="11111111" LName="Boxer" Fname="Ima"/><web_met Role_x0020_ID="22222222" LName="Louis" Fname="Joe"/> <root> What I need is this: <?xml version="1.0"?> <root> <row id="11111111" lname="Boxer" fname="Ima"/> <row id="22222222" lname="Louis" fname="Joe"/> <root> I see there are several approaches to creating XML files. What is the quickest and least complex method to make a simple XML in the required format? Try this SQL, instead of yours:
SELECT cust_ID AS id, LASTNAME AS lname, FIRSTNAME AS fname FROM #web_met FOR XML RAW, ROOT('root') HTH wBob Show quoteHide quote "TLuebke" wrote: > My first time exporting XML via SQL 2005. Searching the net I found a proc > but it doesn't format the file correctly > > Running this: > sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml', > @query = 'SELECT rtrim(cust_ID) AS "Role ID", rtrim(LASTNAME) AS LName, > rtrim(FIRSTNAME) AS Fname FROM web_met for xml auto', > @templatefile = 'c:\temp\template.tpl' > > Produces this: > > <?xml version="1.0"?> > <root><web_met Role_x0020_ID="11111111" LName="Boxer" Fname="Ima"/><web_met > Role_x0020_ID="22222222" LName="Louis" Fname="Joe"/> > <root> > > What I need is this: > > <?xml version="1.0"?> > <root> > <row id="11111111" lname="Boxer" fname="Ima"/> > <row id="22222222" lname="Louis" fname="Joe"/> > <root> > > I see there are several approaches to creating XML files. What is the > quickest and least complex method to make a simple XML in the required format? Bob,
Thanks - that was slick :) how do I get the: <?xml version="1.0"?> on top and the whole thing into a saved text file? Show quoteHide quote "Bob" wrote: > Try this SQL, instead of yours: > > SELECT cust_ID AS id, LASTNAME AS lname, FIRSTNAME AS fname > FROM #web_met > FOR XML RAW, ROOT('root') > > HTH > wBob > > "TLuebke" wrote: > > > My first time exporting XML via SQL 2005. Searching the net I found a proc > > but it doesn't format the file correctly > > > > Running this: > > sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml', > > @query = 'SELECT rtrim(cust_ID) AS "Role ID", rtrim(LASTNAME) AS LName, > > rtrim(FIRSTNAME) AS Fname FROM web_met for xml auto', > > @templatefile = 'c:\temp\template.tpl' > > > > Produces this: > > > > <?xml version="1.0"?> > > <root><web_met Role_x0020_ID="11111111" LName="Boxer" Fname="Ima"/><web_met > > Role_x0020_ID="22222222" LName="Louis" Fname="Joe"/> > > <root> > > > > What I need is this: > > > > <?xml version="1.0"?> > > <root> > > <row id="11111111" lname="Boxer" fname="Ima"/> > > <row id="22222222" lname="Louis" fname="Joe"/> > > <root> > > > > I see there are several approaches to creating XML files. What is the > > quickest and least complex method to make a simple XML in the required format? I got it. I realized that you wanted me to switchout the sql but retain the
other snippet of code. Thanks again for the help Show quoteHide quote "TLuebke" wrote: > Bob, > Thanks - that was slick :) how do I get the: <?xml version="1.0"?> on > top and the whole thing into a saved text file? > > > "Bob" wrote: > > > Try this SQL, instead of yours: > > > > SELECT cust_ID AS id, LASTNAME AS lname, FIRSTNAME AS fname > > FROM #web_met > > FOR XML RAW, ROOT('root') > > > > HTH > > wBob > > > > "TLuebke" wrote: > > > > > My first time exporting XML via SQL 2005. Searching the net I found a proc > > > but it doesn't format the file correctly > > > > > > Running this: > > > sp_makewebtask @outputfile = 'c:\temp\myxmlfile.xml', > > > @query = 'SELECT rtrim(cust_ID) AS "Role ID", rtrim(LASTNAME) AS LName, > > > rtrim(FIRSTNAME) AS Fname FROM web_met for xml auto', > > > @templatefile = 'c:\temp\template.tpl' > > > > > > Produces this: > > > > > > <?xml version="1.0"?> > > > <root><web_met Role_x0020_ID="11111111" LName="Boxer" Fname="Ima"/><web_met > > > Role_x0020_ID="22222222" LName="Louis" Fname="Joe"/> > > > <root> > > > > > > What I need is this: > > > > > > <?xml version="1.0"?> > > > <root> > > > <row id="11111111" lname="Boxer" fname="Ima"/> > > > <row id="22222222" lname="Louis" fname="Joe"/> > > > <root> > > > > > > I see there are several approaches to creating XML files. What is the > > > quickest and least complex method to make a simple XML in the required format?  
Other interesting topics
Obtain Data from XML column
Bulk load and XSD problem... Obtaining data from an XML column to a document changing the results header with for xml Import XML with multiple sublevel nodes XML parsing: ... illegal xml character missing elements while passing XML from .NET to SQL 2005 SQLXML XPath data with apostrophe Need help w/ importing data via web services Use of WITH XMLNAMESPACES |
|||||||||||||||||||||||
