Re: using xml format to import data to sql 2k

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks,

it doesn't work ,
create table mytable
(
field1 int,
field2 varchar(255),
field3 XML
)

"JXStern" <JXSternChangeX2R@xxxxxxx> wrote in message
news:ct27d3pvnkgitai6fnd9ln5f1r7s7lsl5q@xxxxxxxxxx
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



.

Relevant Pages

  • Re: xml reader?
    ... > I apologize in advance if this is not the right place for posting such a ... how to make a XML file user readable? ... > files that are in XML format. ...
    (comp.unix.admin)
  • Re: xml reader?
    ... how to make a XML file user readable? ... >>files that are in XML format. ... That upper arrow points to the very first '<' in front of the first word ...
    (comp.unix.admin)
  • Re: date type format which is supported by excel 2003
    ... Save the file in XML format. ... Check the XML file using a text editor. ... > datetime type there in xsd but it is imoprting it as text.giving error this ...
    (microsoft.public.excel.charting)
  • Re: customised xml document
    ... XML", saving in WordProcessingML format will also save any "Custom XML". ... Of course, once it's in XML format, in theory you can transform it however you like using off-the-shelf XML tools, but I've always suspected that that is significantly harder to do with a complex real-world XML file than is generally believed. ...
    (microsoft.public.word.vba.general)
  • Re: HTML in XML to Word Doc?
    ... but that converts .doc to .html. ... I'm looking to get my HTML converted to Office 2003 XML format, ... templated Office 2003 XML file to display properly when opened by word. ...
    (microsoft.public.word.conversions)