Hi Experts,

Can anybody tell me the code for converting the table
records to a XML document.

I want to write a procedure which will return a string
( varchar(8000) )
containing the xml document.

The Sample XML document i need in the form of string is:

<QATollFreeLocations>
    <LocationName>USA</LocationName>
    <LocationNumber>123456453478</LocationNumber>
</QATollFreeLocations>


Please guide me with an example

Regards,

KG

__________________
KG

Hi KG!

You can use XML auto clause to generate a document-

Select * from [table name] for xml auto

To create document, use this -
osql -Ulogin -Ppasswd -Sserver -Q"select * from table1 for xml auto" -oc:\file1.xml

login : loginame
passwd : password
server : Name of your server

Also remove all the unecessary portions of the file. Such as - hyphens of columns and others from the file.

This works fine. Althoigh, you can directly copy and paste from Query Analyser. You also have optios to use -
[1] xml auto
[2] xml raw
[3] xml explicit

Read Books Online for more info. Do reply if it works or if you have some other help.

-Som Dutt

Hi Som,

I am already using "For XML Auto, Elements" in SQL Server 2000
Problem i am facing is how to save the output in a file or in the string as output.

osql -Ulogin -Ppasswd -Sserver -Q"select * from table1 for xml auto" -oc:\file1.xml

Can u please clear me what is this "osql"
Whats this string ??

u mean to say

-U space and then Login Name
-P space and then password
similarly for -S and -Q

Please gime a clear cut idea..

Thanks for response.
KG




KG

Hi KG!

If you are not joking, I innocently tell you -

OSQL is a command line utility application which is stored in 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn' directory. Use the command line which i have mentioned in -
start > run and type the whole string.
OR
Start > run then type cmd [enter]
Type the string of OSQL and then you will find the output in spedified file. There will be no space between -U -S and -P options.

The file may contain some spaces and hyphens remove them.
If you ask me write a VB program that will automatically create a xml file based on your query, I can write that for you.

But, I think you must be known of OSQL and you must be joking. I liked that ..

Dear Som,

U were rite. The osql utility u suggested me is a manual prcocess.

osql -U -P -S -d -Q "Select * from Table1 for xml auto, elements" -o c:\file1.xml

-d was missing, its for database we need to opt.

Tnx for responding me.

I am using C# and wrote a code like:

StringWriter strWriter = new StringWriter();
XmlTextWriter xmlWriter = new XmlTextWriter(strWriter);

xmlWriter.WriteStartElement("QA"); // create a common root for all the tables

SqlConnection mySqlConnection;
mySqlConnection = new SqlConnection(sConnection);
mySqlConnection.Open();

XmlReader xmlReader = new SqlCommand("Select * From Table1 FOR XML AUTO, Elements", mySqlConnection).ExecuteXmlReader();
xmlWriter.WriteNode(xmlReader, true);

xmlReader.Close();
xmlWriter.WriteEndElement();

mySqlConnection.Close();

Problem is how do i convert the XML string into a XML file ???

Tnx n Regards,

KG

Hi KG!

Use full qualified name for the table. Such as -
database.dbo.table1

Although, -d option can also be used, but, you can not use other database tables.

I have no idea of coding in C#. But, the output file (file1.xml in my example) can be altered in a xml document. That is a regular file. You can edit it yourself in Notepad. Apply Header and some other strings suitable for your purpose.