importing XML/ASCII files to SQl database using VB express

Hi everyone,

I have to write a program in VB to receive the read data from a RFID reader for my graduation project.The problem is I am not a computer science student so I have only general info on programming.

I created my DB in VB express but I couldn't find out how to send the read data (that will be either in XML or ASCII format) to my database...The read data will be transferred to my computer by the RFID reader's software but after that I don'T know how to transfer it to my DB.As I know I have to use commands like read.xml etc,but no idea how write the complete program.

I checked the forum and couldn't find the answer,sorry if someone already answered my question and I missed it.

Thanks...

Can

i suspect that you will find greater success if you post your question here: http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=38&SiteID=1|||

ok,thanks,now I 'll do it...

Can

Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.

Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.

Importing Xml with SqlBulkCopy

I am looking into importing Xml into SQL Server 2005. The Xml files are about 30MB each, and I need the import to work fast.

The Xml is only records with fields (no real hierarchy) i.e.

<books>
<book>
<title>Harry Potter</title>
<author>JK Rowling</author>
etc etc
</book>
</books>

I am looking into doing in a C# application that will use SqlBulkCopy. However as far I can see the WriteToServer method can only accept either a DataRow[], DataTable or IDataReader.

Obviously it is very easy to convert my Xml into a DataTable to DataRow, but these are both in-memory, so they will memory intensive and slow.

Is there an easy way to expose my Xml through an IDataReader object? Like a wrapper over XmlTextReader? I have thought I could write a custom wrapper over XmlTextReader that supports the IDataReader interface - but I keep thinking what I am doing is pretty basic and I must be missing an easier solution? Anybody ideas?

If not, can anybody recommend an alternative solution for getting these records in extremely quickly programmatically?

AndrewYou can use the OPENXML command to achieve this, You would either have to pass the XML as a string to a procedure or have the file in a place SQL could access.|||I looked into OPENXML but have received several warnings regarding its performance. I wanted the fastest way of importing without consuming a lot of resources.

In the end I opted to use SQLXMLBulkLoad and it works very nice and quite elegantly (using a schema to map XML elements to SQL fields.

Andrew|||

I thought there was an XML bulk load option just couldn't find it.

Would you care to post your schema and code so this question has a complete answer.

|||

You maybe can use SQLXMLBULKLOAD .The sample code (c#)

public bool BulkLoad(string ConnectionStr, string MyXsdFile, string MyXMLFile)
{
bool test;
try
{
SQLXMLBulkLoad4 objBL = new SQLXMLBulkLoad4();
objBL.ConnectionString = @."provider=SQLOLEDB;data source=SHA-WKS1333\SQL2005;database=MatrixTest;integrated security=SSPI;";
objBL.ErrorLogFile = @.".\error.log";
objBL.CheckConstraints = true;
test = objBL.Transaction;
objBL.XMLFragment = true;
objBL.TempFilePath = @.".\";
objBL.SchemaGen = true;
objBL.SGDropTables = true;
objBL.Execute(MyXsdFile, MyXMLFile);
return true;
}
catch (Exception e)
{
MessageBox.Show("{0} Exception caught." + e);
}
return false;
}

and you can find more info about SQLXMLBULKLOAD in MSDN.

|||Could you please send the code which you have used to achieve this.sql

Importing xml through tsql

Hey
I'm relatively new to xml.
I want to upload xml data with sql's openxml. I can do it with "normal" xml
but my files look a bit different and I can't find help on it.
My file looks like this:
<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>
Hope this makes sense.
Thanks for the help.
Assuming you want the data in two different tables, you could write:
declare @.h int
exec sp_xml_preparedocument @.h output, N'<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>'
--insert into table1
select * from OpenXML(@.h, '/myfile/table1',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
--insert into table2
select * from OpenXML(@.h, '/myfile/table2',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
exec sp_xml_removedocument @.h
HTH
Michael
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:D66EB2F0-A4F4-4CAB-904C-B4550359905B@.microsoft.com...
> Hey
> I'm relatively new to xml.
> I want to upload xml data with sql's openxml. I can do it with "normal"
> xml
> but my files look a bit different and I can't find help on it.
> My file looks like this:
> <myfile>
> <table1>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table1>
> <table2>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table2>
> </myfile>
> Hope this makes sense.
> Thanks for the help.
>

Importing xml through tsql

Hey
I'm relatively new to xml.
I want to upload xml data with sql's openxml. I can do it with "normal" xml
but my files look a bit different and I can't find help on it.
My file looks like this:
<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>
Hope this makes sense.
Thanks for the help.Assuming you want the data in two different tables, you could write:
declare @.h int
exec sp_xml_preparedocument @.h output, N'<myfile>
<table1>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table1>
<table2>
<col1>Value1</col1>
<col2>Value2</col2>
<col3>Value3</col3>
<col4>Value4</col4>
</table2>
</myfile>'
--insert into table1
select * from OpenXML(@.h, '/myfile/table1',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
--insert into table2
select * from OpenXML(@.h, '/myfile/table2',2)
with (col1 nvarchar(20),col2 nvarchar(20),col3 nvarchar(20),col4
nvarchar(20))
exec sp_xml_removedocument @.h
HTH
Michael
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:D66EB2F0-A4F4-4CAB-904C-B4550359905B@.microsoft.com...
> Hey
> I'm relatively new to xml.
> I want to upload xml data with sql's openxml. I can do it with "normal"
> xml
> but my files look a bit different and I can't find help on it.
> My file looks like this:
> <myfile>
> <table1>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table1>
> <table2>
> <col1>Value1</col1>
> <col2>Value2</col2>
> <col3>Value3</col3>
> <col4>Value4</col4>
> </table2>
> </myfile>
> Hope this makes sense.
> Thanks for the help.
>

Importing XML into SQL using DTS

I need to import large XML files into an SQL table.
My XML experience is minimal...
My current DTS script can import xml files which are more structured (using
NODES) and works fine. How ever i need to modify it to look at Attributes
instead of nodes.
It looks like this:
'************************************************* *********************
' Visual Basic ActiveX Script
'************************************************* ***********************
Function Main()
Dim objXMLDOM
Dim objNodes
Dim objBookNode
Dim objADORS
Dim objADOCnn
Const adOpenKeyset = 1
Const adLockOptimistic = 3
Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
objXMLDOM.async = False
objXMLDOM.validateOnParse = False
'No error handling done
objXMLDOM.load
"U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
Set objNodes = objXMLDOM.selectNodes("/Books/Book")
Set objADOCnn = CreateObject("ADODB.Connection")
Set objADORS = CreateObject("ADODB.Recordset")
objADOCnn.Open
"PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
adOpenKeyset, adLockOptimistic
For Each objBookNode In objNodes
With objADORS
.AddNew
.fields("BookTitle") = objBookNode.selectSingleNode("Title").nodeTypedVal ue
.fields("Publisher") =
objBookNode.selectSingleNode("Publisher").nodeType dValue
.fields("DateOfPurchase") =
objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
.Update
End With
Next
objADORS.Close
objADOCnn.Close
Main = DTSTaskExecResult_Success
End Function
#################
How do i modify it to look at an XML file structured using Attributes?
...XML File looks like
The xml structure looks like this:
<?xml version="1.0" encoding="utf-8"?>
<usageFile source="abc" countRetrievals="12345" countSearches="0"
fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
<BookTitle="abc123" Publisher="abcdef"
DateOfPurchase="2005-05-16T05:45:36"/>
Thanks for the help
Did you try to use '@.Publisher' instead of 'Publisher' in your path
expression?
Best regards
Michael
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help
|||Try to use attributes property on Dom nodes to get the values of attributes:
http://msdn.microsoft.com/library/de...65757ceb24.asp
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Fec" <Fec@.discussions.microsoft.com> wrote in message
news:E454BA66-8786-403D-BC80-406028CCFDAE@.microsoft.com...
>I need to import large XML files into an SQL table.
> My XML experience is minimal...
> My current DTS script can import xml files which are more structured
> (using
> NODES) and works fine. How ever i need to modify it to look at Attributes
> instead of nodes.
> It looks like this:
> '************************************************* *********************
> ' Visual Basic ActiveX Script
> '************************************************* ***********************
> Function Main()
> Dim objXMLDOM
> Dim objNodes
> Dim objBookNode
> Dim objADORS
> Dim objADOCnn
> Const adOpenKeyset = 1
> Const adLockOptimistic = 3
> Set objXMLDOM = CreateObject("MSXML2.DOMDocument.4.0")
> objXMLDOM.async = False
> objXMLDOM.validateOnParse = False
> 'No error handling done
> objXMLDOM.load
> "U:\2-Data\RStation\Unprocessed\bns_usage_2005-05-16_bns2ha.xml"
> Set objNodes = objXMLDOM.selectNodes("/Books/Book")
> Set objADOCnn = CreateObject("ADODB.Connection")
> Set objADORS = CreateObject("ADODB.Recordset")
> objADOCnn.Open
> "PROVIDER=SQLOLEDB;SERVER=UKIW0004921G\LOCAL;UID=s a;PWD=bigbird;DATABASE=ImportXML;"
> objADORS.Open "SELECT * FROM tmpImportXML WHERE 1 = 2", objADOCnn,
> adOpenKeyset, adLockOptimistic
> For Each objBookNode In objNodes
> With objADORS
> .AddNew
> .fields("BookTitle") =
> objBookNode.selectSingleNode("Title").nodeTypedVal ue
> .fields("Publisher") =
> objBookNode.selectSingleNode("Publisher").nodeType dValue
> .fields("DateOfPurchase") =
> objBookNode.selectSingleNode("DateOfPurchase").nod eTypedValue
> .Update
> End With
> Next
> objADORS.Close
> objADOCnn.Close
> Main = DTSTaskExecResult_Success
> End Function
> #################
> How do i modify it to look at an XML file structured using Attributes?
> ...XML File looks like
> The xml structure looks like this:
> <?xml version="1.0" encoding="utf-8"?>
> <usageFile source="abc" countRetrievals="12345" countSearches="0"
> fileStart="2005-05-16T05:46:36" fileEnd="2005-05-16T07:00:00">
> <BookTitle="abc123" Publisher="abcdef"
> DateOfPurchase="2005-05-16T05:45:36"/>
> --
>
> Thanks for the help

Importing XML for Newbies

I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA fo
r
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.DaveK wrote:
> I need to store log files that are created in XML format so I can query so
me
> of the fields.
Why not just use an XQuery implementation to query them direct? I see no
requirement here to involve a database at all.
///Peter

> I have only minor knowledge of XML, but have been SQL DBA for
> a while. I just have not had a need to use XML in the SQL environment. W
e
> are using SQL2000. What is the best way to do the following:
> 1. Create a DB for storing the log files? Some variation on the usual way
?
> 2. Import the individual files into the DB. Eventually I can write a DTS
or
> something to import?
> 3. Query the DB. Create some reports on usage.|||After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but th
e
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.|||DaveK wrote:
> After a five minute web search and SQL help search I don't see how I can
> avoid using SQL to build some sort of datbase just so I can address backup
,
> security, etc. It looks like XPath and XQuery can do some searching, but
the
> other items that I need to cover are not really addressed. This is an
> example of one event log item I want to store.
> <CreateDate>7/31/2007</CreateDate>
> <CreateTime>10:19:25</CreateTime>
> <Logger>xxxxd7yrrt11</Logger>
> <Events>
> <Event>
> <EventID>8001</EventID>
> <Description>Login</Description>
> <Category>Audit</Category>
> <Source>LAN Client</Source>
> <SubSource>xxxx_8</SubSource>
> <UserName>test.name</UserName>
> <UserID>347</UserID>
> <Computer>xxx7YRRT11</Computer>
> <Date>07/31/2007</Date>
> <Time>10:19:49</Time>
> <ObjectType>User</ObjectType>
> <Details></Details>
> </Event>
> I have to admit I am not a convert to the XML world, but as I said, I am a
> newbie to it as well. In this case it just seems like a fancy way to
> eliminate delimited importing. The format is not likely to change.
AFAIK all database systems now offer some kind of "Import XML" plugin.
If yours doesn't, you'll need to turn the XML into CSV or whatever your
system consumes. The easiest way to do this is to write an XSLT script,
and I think there are several quoted or linked in Dave Pawson's XSL FAQ
at http://www.dpawson.co.uk/xsl/
XML is just a fancy way of identifying information: you can see from the
above example that it is much clearer in naming items and positioning
them in the hierarchy than (for example) CSV. If the format is stable,
then a little routine to run XSLT over the data and spit out CSV should
do you just fine.
The following appears to work for the sample above (with the addition of
the missing </Events> end-tag and the enclosing root element
<data>...</data> ):
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
version="1.0">
<xsl:output method="text"/>
<xsl:strip-space elements="*"/>
<xsl:template match="/">
<xsl:apply-templates select="data/CreateDate"/>
<xsl:apply-templates select="data/CreateTime"/>
<xsl:apply-templates select="data/Logger"/>
<xsl:apply-templates select="data/Events/Event/*"/>
</xsl:template>
<!-- fields that start a record -->
<xsl:template match="CreateDate|EventID">
<xsl:text>"</xsl:text>
<xsl:value-of select="."/>
</xsl:template>
<!-- fields that occur in mid-record -->
<xsl:template match="*">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>"</xsl:text>
</xsl:template>
<!-- fields that end a record -->
<xsl:template match="Logger|Details">
<xsl:text>,"</xsl:text>
<xsl:value-of select="."/>
<xsl:text>" </xsl:text>
</xsl:template>
</xsl:stylesheet>
$ java -jar /usr/local/saxon/b8.5/saxon8.jar -o test.csv test.xml test.xsl
$ cat test.csv
"7/31/2007,"10:19:25","xxxxd7yrrt11"
"8001,"Login","Audit","LAN
Client","xxxx_8","test.name","347","xxx7YRRT11","07/31/2007","10:19:49","Use
r",""
$
This makes the assumption that your import routine can do something
different with record #1...
///Peter
--
XML FAQ: http://xml.silmaril.ie/|||Thank you. This is very helpful. I did not paste in the whole log so misse
d
the ending tags.

Importing XML for Newbies

I need to store log files that are created in XML format so I can query some
of the fields. I have only minor knowledge of XML, but have been SQL DBA for
a while. I just have not had a need to use XML in the SQL environment. We
are using SQL2000. What is the best way to do the following:
1. Create a DB for storing the log files? Some variation on the usual way?
2. Import the individual files into the DB. Eventually I can write a DTS or
something to import?
3. Query the DB. Create some reports on usage.
If somene could post a link to get me started it would be very helpful.
After a five minute web search and SQL help search I don't see how I can
avoid using SQL to build some sort of datbase just so I can address backup,
security, etc. It looks like XPath and XQuery can do some searching, but the
other items that I need to cover are not really addressed. This is an
example of one event log item I want to store.
<CreateDate>7/31/2007</CreateDate>
<CreateTime>10:19:25</CreateTime>
<Logger>xxxxd7yrrt11</Logger>
<Events>
<Event>
<EventID>8001</EventID>
<Description>Login</Description>
<Category>Audit</Category>
<Source>LAN Client</Source>
<SubSource>xxxx_8</SubSource>
<UserName>test.name</UserName>
<UserID>347</UserID>
<Computer>xxx7YRRT11</Computer>
<Date>07/31/2007</Date>
<Time>10:19:49</Time>
<ObjectType>User</ObjectType>
<Details></Details>
</Event>
I have to admit I am not a convert to the XML world, but as I said, I am a
newbie to it as well. In this case it just seems like a fancy way to
eliminate delimited importing. The format is not likely to change.
|||Thank you. This is very helpful. I did not paste in the whole log so missed
the ending tags.

Importing XML Files to SQL Database using SSIS

Hi,
I need to Import an XML file into SQL Database using SSIS, Plz let me know
how do I acheive this. I am quite new to SSIS, So if you can Point me to some
samples on importing XML files using SSIS, nothing like that. Any additional
references, if any, are most welcome.
Thanks in Advance,
Sachin R. Chavan.
Hello Sachin,

> I need to Import an XML file into SQL Database using SSIS, Plz let me
> know how do I acheive this. I am quite new to SSIS, So if you can
> Point me to some samples on importing XML files using SSIS, nothing
> like that. Any additional references, if any, are most welcome.
a. Get this book and read it: http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584359.html
b. Use the XML source.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
sql

Importing XML Files to SQL Database using SSIS

Hi,
I need to Import an XML file into SQL Database using SSIS, Plz let me know
how do I acheive this. I am quite new to SSIS, So if you can Point me to som
e
samples on importing XML files using SSIS, nothing like that. Any additional
references, if any, are most welcome.
Thanks in Advance,
Sachin R. Chavan.Hello Sachin,

> I need to Import an XML file into SQL Database using SSIS, Plz let me
> know how do I acheive this. I am quite new to SSIS, So if you can
> Point me to some samples on importing XML files using SSIS, nothing
> like that. Any additional references, if any, are most welcome.
a. Get this book and read it: http://www.wrox.com/WileyCDA/WroxTi...>
4584359.html
b. Use the XML source.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Importing XML data

Hi Experts,
How to import data in XML files into SQL Server 2000?
Thanks,
Hari
Hi,
Have a look into the below link. ( Describes the various methods to load XML
to SQl 2000 tables)
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:283101c47dd6$d1af5aa0$a601280a@.phx.gbl...
> Hi Experts,
> How to import data in XML files into SQL Server 2000?
> Thanks,
> Hari

Importing XML data

Hi Experts,
How to import data in XML files into SQL Server 2000?
Thanks,
HariHi,
Have a look into the below link. ( Describes the various methods to load XML
to SQl 2000 tables)
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:283101c47dd6$d1af5aa0$a601280a@.phx.gbl...
> Hi Experts,
> How to import data in XML files into SQL Server 2000?
> Thanks,
> Hari

Importing XML data

Hi Experts,
How to import data in XML files into SQL Server 2000?
Thanks,
HariHi,
Have a look into the below link. ( Describes the various methods to load XML
to SQl 2000 tables)
http://www.perfectxml.com/articles/xml/importxmlsql.asp
Thanks
Hari
MCDBA
"Hari" <anonymous@.discussions.microsoft.com> wrote in message
news:283101c47dd6$d1af5aa0$a601280a@.phx.gbl...
> Hi Experts,
> How to import data in XML files into SQL Server 2000?
> Thanks,
> Hari

importing xml

How do I set up a routine to automatically import data from xml files into a
database table in sql server 7? All the solutions I have looked at seem to be
using Sql server 2000 so I'm not quite sure where to start - please help...
SQL Server 7 has no such support. You may find some third party tool that
may be able to help you.
Best regards
Michael
"Humera" <Humera@.discussions.microsoft.com> wrote in message
news:E4B689C7-D63E-4697-9F6A-D5A6815D34CD@.microsoft.com...
> How do I set up a routine to automatically import data from xml files into
> a
> database table in sql server 7? All the solutions I have looked at seem to
> be
> using Sql server 2000 so I'm not quite sure where to start - please
> help...
|||ok thanks for that michael - will have a look to see if I can find a tool
that will do that. Otherwise would it be possible to convert the xml to a
different format, maybe a txt file, and then link this into the database. As
the files will be ftp'd onto our server throughout the day I need something
to automatically run at certain times and run the procedure.
Humera.
"Michael Rys [MSFT]" wrote:

> SQL Server 7 has no such support. You may find some third party tool that
> may be able to help you.
> Best regards
> Michael
> "Humera" <Humera@.discussions.microsoft.com> wrote in message
> news:E4B689C7-D63E-4697-9F6A-D5A6815D34CD@.microsoft.com...
>
>
|||This depends.
You basically have two options:
1. You just want to store the XML as a CLOB/BLOB. You can just load it using
the normal SQL 7 mechanisms into an NTEXT/IMAGE field.
2. You want to shred it into relational parts. In SQL 7, you will need to do
this on the client-side and then send it to the database in relational form.
The automatization would have to be build as a mid-tier component on the
client-side of the database in either case. You probably want to build it
asynchronously, building some queues and then have worker threads that will
perform the loading.
Best regards
Michael
"Humera" <Humera@.discussions.microsoft.com> wrote in message
news:59381C6D-7D5A-41F9-B41A-5880A177205A@.microsoft.com...[vbcol=seagreen]
> ok thanks for that michael - will have a look to see if I can find a tool
> that will do that. Otherwise would it be possible to convert the xml to a
> different format, maybe a txt file, and then link this into the database.
> As
> the files will be ftp'd onto our server throughout the day I need
> something
> to automatically run at certain times and run the procedure.
> Humera.
> "Michael Rys [MSFT]" wrote:
sql

Importing txt files with sql server 2000

HI!

I am importing .txt files. How can i check the errors? I have created a
log file, but the problem is that i lose some characters.
I import for example:

Code
ABC
FZH
JHN

from a text file, but sometimes Code can be 4 caracters long
I import this 3 characters long now. When i add the same structured
text file with some rows lenght 4, it skips the last character, but i
get nothing in the log file.

please help
xgirlSo you already have a table after importing items with 3 characters,
and then later you import items with 4 characters?

Perhaps it set the field width to 3 based on what it found in the first
import.

Sounds like you may need to manually widen the field. Open the table
in design view and make sure the field width is large enough for 4
characters.|||If you are using a DTS for the import, you may want to check the
Transform Data Task. If you are using fixed width delimiting then that
may be the problem.|||I changed from varchar ->nvarchar if you mean that but still i get no
errors of lost characters.

The problem is i have a lot of .txt files and i will get in the future
the same files with different data. if i automaticlly import every
file, how can i be sure the data are not longer and i didn't lost some
characters.

thank you
xgirl|||I changed from varchar ->nvarchar if you mean that but still i get no
errors of lost characters.

The problem is i have a lot of .txt files and i will get in the future
the same files with different data. if i automaticlly import every
file, how can i be sure the data are not longer and i didn't lost some
characters.

thank you
xgirl

Importing TXT files problems

Hello,
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JB
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it in a better format. Unfortunately I have over 100,000 records, which rules EXCEL out. I think BULK IMPORT and BCP can't import files based on character widths but could use tabs
as you suggested. Using VB to write an import script could be an option though.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble making it work correctly. It is a ASCII text file where the fields vary by the number of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CITY i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field breaks to be. Then it imports everything as Characters with column headers of Col001, Col002 etc. I don't want everything as characters or Col001 etc. Some columns would b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB
|||The columns are fixed length. For example Column1 is 5 characters long, Column2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 characters long, etc. Also, some of the columns are numeric and integer as well but are represented by chara
cters until imported.
Thanks,
JB

Importing TXT files problems

Hello,
I'm fairly new to SQL and hopefully this is a simple question to answer.
I've been trying to import a TXT file into an SQL database and I'm having tr
ouble making it work correctly. It is a ASCII text file where the fields var
y by the number of characters. This can be 2 characters up to 40 (STATE woul
d be 2 characters, CITY is
32 characters, etc.)
I can import the file with DTS. I go in and select exactly where I want the
field breaks to be. Then it imports everything as Characters with column hea
ders of Col001, Col002 etc. I don't want everything as characters or Col001
etc. Some columns would be
INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't
import the data correctly.
Also, I have an SQL script that I wrote for a table where I can create the f
ield lengths, data type etc., FWIW. This seems to be going nowhere fast.
What am I doing wrong? Should I be using something else than DTS?
Any suggestions are greatly appreciated.
Thanks,
JBWe have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...
"JonBlack" <anonymous@.discussions.microsoft.com> wrote in message
news:AE462262-F98D-40A1-BD57-6E2066BE2A6D@.microsoft.com...
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having
trouble making it work correctly. It is a ASCII text file where the fields
vary by the number of characters. This can be 2 characters up to 40 (STATE
would be 2 characters, CITY is 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want
the field breaks to be. Then it imports everything as Characters with column
headers of Col001, Col002 etc. I don't want everything as characters or
Col001 etc. Some columns would be INT, NUMERIC(x,x), etc. but everytime I
change these values in DTS it won't import the data correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||Hi Steve,
Thanks for your reply. I wouldn't mind importing it into EXCEL and saving it
in a better format. Unfortunately I have over 100,000 records, which rules
EXCEL out. I think BULK IMPORT and BCP can't import files based on character
widths but could use tabs
as you suggested. Using VB to write an import script could be an option thou
gh.
Thanks again,
JB
-- Steve Z wrote: --
We have had really good luck with BULK INSERT using TAB DELIMITED data.
Maybe you can get it to be TAB DELIMITED using EXCEL - IMPORT the text and
save as .TDF.
We also are a VB shop, so on top of that we have developed several different
versions of a TEXTIMPORT program - some that take the lengths of each SQL
column and cut the input data based on that, others that look for specific
items while cutting up the data. This way we can also deal with odd data
issues for various columns (the TEXTIMPORT uses a little .INI file that
controls specifics like that).
I believe that BULK INSERT and BCP have control files to allow greater
flexibility - maybe check BOL (books online)...|||Is it a column a fix length or delimited by coma ?
JonBlack wrote:
> Hello,
> I'm fairly new to SQL and hopefully this is a simple question to answer.
> I've been trying to import a TXT file into an SQL database and I'm having trouble
making it work correctly. It is a ASCII text file where the fields vary by the numbe
r of characters. This can be 2 characters up to 40 (STATE would be 2 characters, CIT
Y i
s 32 characters, etc.)
> I can import the file with DTS. I go in and select exactly where I want the field
breaks to be. Then it imports everything as Characters with column headers of Col001
, Col002 etc. I don't want everything as characters or Col001 etc. Some columns woul
d b
e INT, NUMERIC(x,x), etc. but everytime I change these values in DTS it won't import the dat
a correctly.
> Also, I have an SQL script that I wrote for a table where I can create the
field lengths, data type etc., FWIW. This seems to be going nowhere fast.
> What am I doing wrong? Should I be using something else than DTS?
> Any suggestions are greatly appreciated.
> Thanks,
> JB|||The columns are fixed length. For example Column1 is 5 characters long, Colu
mn2 is 2 characters long, ColumnC is 30 characters long, ColumnD is 10 chara
cters long, etc. Also, some of the columns are numeric and integer as well b
ut are represented by chara
cters until imported.
Thanks,
JB

Importing textfiles in to text fields how ?

Hello,
I have text files.
(Less than a hundred files, sizes between 3 K and 150 K)
I would like to import these files into the database, first
in a table called : Long_text_table.
Each text file goes into only one field.
The id and label fields will be assigned by hand with the
correct values, so that the long text can be moved to
the correct field in the destination table with an sql statement.
How can I get the texts in to this Long_text_table ?
(Using the standard SQL-server tools).
ben brugman
The import table will be something like :
CREATE TABLE [dbo].[Long_text_table] (
[id1] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NULL ,
[id3] [int] NULL ,
[label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
You can do it with DTS as outlined in
http://www.sqldts.com/?246
Set the field and row delimiter of the Import text file to a combination of
characters that you are sure do not appear in the text file and the whole
file will be treated as one field.
You can just type over the list with delimiters in the DTS designer, you are
not limited to {CR}{LF} etc.
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have text files.
> (Less than a hundred files, sizes between 3 K and 150 K)
> I would like to import these files into the database, first
> in a table called : Long_text_table.
> Each text file goes into only one field.
> The id and label fields will be assigned by hand with the
> correct values, so that the long text can be moved to
> the correct field in the destination table with an sql statement.
> How can I get the texts in to this Long_text_table ?
> (Using the standard SQL-server tools).
> ben brugman
>
> The import table will be something like :
> CREATE TABLE [dbo].[Long_text_table] (
> [id1] [int] IDENTITY (1, 1) NOT NULL ,
> [id2] [int] NULL ,
> [id3] [int] NULL ,
> [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
|||Thanks for your attention,
I'll have a look into this. (On the first glance this is not familiar to
me).
For importing files in a single field I have found
the DTS readfile transformation. I tried this with one file
and it works.
Next week I'll try to integrate both methods.
(If I understand the example then).
Otherwise I have to type the filenames by hand one at the
time. (Not a huge problem).
thanks again
ben
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote
in message news:ubR5bS8pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> You can do it with DTS as outlined in
> http://www.sqldts.com/?246
> Set the field and row delimiter of the Import text file to a combination
of
> characters that you are sure do not appear in the text file and the whole
> file will be treated as one field.
> You can just type over the list with delimiters in the DTS designer, you
are
> not limited to {CR}{LF} etc.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
>

Importing textfiles in to text fields how ?

Hello,
I have text files.
(Less than a hundred files, sizes between 3 K and 150 K)
I would like to import these files into the database, first
in a table called : Long_text_table.
Each text file goes into only one field.
The id and label fields will be assigned by hand with the
correct values, so that the long text can be moved to
the correct field in the destination table with an sql statement.
How can I get the texts in to this Long_text_table ?
(Using the standard SQL-server tools).
ben brugman
The import table will be something like :
CREATE TABLE [dbo].[Long_text_table] (
[id1] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NULL ,
[id3] [int] NULL ,
[label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOYou can do it with DTS as outlined in
http://www.sqldts.com/?246
Set the field and row delimiter of the Import text file to a combination of
characters that you are sure do not appear in the text file and the whole
file will be treated as one field.
You can just type over the list with delimiters in the DTS designer, you are
not limited to {CR}{LF} etc.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have text files.
> (Less than a hundred files, sizes between 3 K and 150 K)
> I would like to import these files into the database, first
> in a table called : Long_text_table.
> Each text file goes into only one field.
> The id and label fields will be assigned by hand with the
> correct values, so that the long text can be moved to
> the correct field in the destination table with an sql statement.
> How can I get the texts in to this Long_text_table ?
> (Using the standard SQL-server tools).
> ben brugman
>
> The import table will be something like :
> CREATE TABLE [dbo].[Long_text_table] (
> [id1] [int] IDENTITY (1, 1) NOT NULL ,
> [id2] [int] NULL ,
> [id3] [int] NULL ,
> [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>|||Thanks for your attention,
I'll have a look into this. (On the first glance this is not familiar to
me).
For importing files in a single field I have found
the DTS readfile transformation. I tried this with one file
and it works.
Next week I'll try to integrate both methods.
(If I understand the example then).
Otherwise I have to type the filenames by hand one at the
time. (Not a huge problem).
thanks again
ben
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ubR5bS8pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> You can do it with DTS as outlined in
> http://www.sqldts.com/?246
> Set the field and row delimiter of the Import text file to a combination
of
> characters that you are sure do not appear in the text file and the whole
> file will be treated as one field.
> You can just type over the list with delimiters in the DTS designer, you
are
> not limited to {CR}{LF} etc.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > I have text files.
> > (Less than a hundred files, sizes between 3 K and 150 K)
> >
> > I would like to import these files into the database, first
> > in a table called : Long_text_table.
> >
> > Each text file goes into only one field.
> > The id and label fields will be assigned by hand with the
> > correct values, so that the long text can be moved to
> > the correct field in the destination table with an sql statement.
> >
> > How can I get the texts in to this Long_text_table ?
> > (Using the standard SQL-server tools).
> >
> > ben brugman
> >
> >
> > The import table will be something like :
> >
> > CREATE TABLE [dbo].[Long_text_table] (
> > [id1] [int] IDENTITY (1, 1) NOT NULL ,
> > [id2] [int] NULL ,
> > [id3] [int] NULL ,
> > [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> >
>