Re: using xml format to import data to sql 2k
- From: JXStern <JXSternChangeX2R@xxxxxxx>
- Date: Mon, 27 Aug 2007 19:53:31 -0700
On Mon, 27 Aug 2007 11:53:21 -0400, "mecn" <mecn2002@xxxxxxxxx> wrote:
Hi,
1. Instead of text delimiter, an xml format data could imported to sql 2k
tables?
Yes, you can easily import XML data.
2. The same way import text files? --fast and less resource? what is the
benefit of using xml instead of T-sql
There are plusses and minuses.
XML files tend to be MUCH larger than CSV or plain-text files.
It can sometimes be difficult to convert XML data to fully relational
data.
You can lose the purity of the relational model, if you leave the data
in its XML format.
3. How do I generate an xml format table schma?
There are many ways.
In the simplest, you don't really do anything, just create a table
with an XML column, and read the whole XML file into that column!
create table mytable
(
field1 int,
field2 varchar(255),
field3 XML
)
Then you can read an entire XML file into it with something like:
INSERT INTO mytable(field1, field2, field3)
SELECT 1, 'foo', * FROM OPENROWSET(
BULK 'c:\SampleFolder\SampleData3.xml',
SINGLE_BLOB) AS x
http://msdn2.microsoft.com/en-us/library/ms191184.aspx
There is a *lot* of stuff in SQLServer 2005 to support XML.
I'm just learning it myself.
It can be very confusing, but is also very powerful.
Josh
.
- Follow-Ups:
- References:
- using xml format to import data to sql 2k
- From: mecn
- using xml format to import data to sql 2k
- Prev by Date: using xml format to import data to sql 2k
- Next by Date: Re: in sql server is it possible to get the amount of memory used by a stored procedure? e.g. memory usage statistics per stored procedure?
- Previous by thread: using xml format to import data to sql 2k
- Next by thread: Re: using xml format to import data to sql 2k
- Index(es):
Relevant Pages
|
