• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 480
  • Last Modified:

SQL SERVER SP --> XML OUTPUT -->USE VB.NET --> MULTI LANGUAGE XML CREATION

Hi XML Gurus,

We have a situation here.  We have the SP from SQL SERVER 2000 to create the XML OUTPUT.  THis contains multiple orders (one order to many lines);

We are using a VB.NET program to read this as a stream and create individual XMLs based upon the order.  ie., One XML file per order that has many lines.  

Exmaple: If there are 10 orders with many lines, it will create 10 different XML files.

We have the perfect UTF-8 output file from the SP.  NO issues here.  We can take output in a text format (OEM method) and view it in IE or MS WORD and successfully see the Japanese characters.

However, we just cannot reanme this file to XML as it throws an error .  We tried with UTF8 encoding too; no luck.

So, gurus:
1) Do we need to convert this text file taken out from the SQL SERVER 2000 via DTS package (OEM method)?
2) Does the VB.NET program need any special comonenents require for this?.

Someone in the forum has suggested to look into http://www.chilkatsoft.com/ChilkatCharset.asp to convert from a Text file to XML.

Will this work?.

OR is there any other suggestions?.

Pl. help us out.

Thanks, VP
0
jsrams
Asked:
jsrams
1 Solution
 
rdcproCommented:
Without seeing what you're producing, I don't think I can answer specifically.  But if your SP produces data using a Select, such as:

Select * from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID

then you can get XML fairly easily using the FOR XML clause:

Select * from Customers inner join Orders on Customers.CustomerID = Orders.CustomerID  FOR XML AUTO

You can read this in as a stream, and it will preserve encoding just fine.  Once you have the XML as a stream, you're ready to do anything you want with it.  But I would avoid turning any part of it into a string, because you'll have to watch your encoding carefully.

It would help if you could post a little of what you're calling "text format".  If it's plain text, with no XML markup, then it can't simply be renamed to XML. If it "looks" like XML, that's another story.

In answer to #2, VB.NET doesn't exactly *require* any special components, but there is a toolset that I often use, called SQLXML, that can be used directly from VB.NET, and it provides a number of very nice ways of working with XML and SQL Server 2000.  If you have the .NET runtime installed when you install SQLXML, you'll get a set of managed classes you can use from .NET

http://www.microsoft.com/downloads/details.aspx?FamilyID=51d4a154-8e23-47d2-a033-764259cfb53b&DisplayLang=en

Once it's installed, you'll have a new program group called SQLXML 3.0 and it has an IIS configuration utility, and an excellent set of documentation on how it's used.  

Regards,
Mike Sharp

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now