Importing Access DB: table

Showing posts with label table. Show all posts

Friday, March 30, 2012

imports table structures in SQL 2000 into Excel

Hi.
Is there anyway to export the table structures : data type,length,NULLABLE,Description into an Excel file using MS SQL Server?

Or I need to do it manually?
Thank you in advanced.
Sincerely

AgustinaRun this in Query Analyzer: (common data types, add the the case statement for more)


select name,
case xtype
 when 56 then 'Int'
 when 127 then 'BigInt'
 when 167 then 'VarChar'
 when 175 then 'Char'
 when 60 then 'Money'
 when 58 then 'SmallDateTime'
 when 104 then 'Bit'
 when 173 then 'TimeStamp'
 when 61 then 'DateTime'
 when 48 then 'TinyInt'
 else 'Other' end,
length
from syscolumns
where id = (
select id
from sysobjects
where name = 'TheTableName')
order by colid

|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_CATALOG = '<DATABASE NAME>' AND TABLE_SCHEMA = '<DB OWNER>' AND TABLE_NAME = '<YOUR TABLES NAME>'

sql

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 file

Hello there
I'm new on xml
I have xml file in 500MB size, which i want to import it to table on sql
server
how can i do that?How exactly do you want to get it into a table? As an XML datatype instance
in a row? Or are you planning on shredding information into one or multiple
tables? Do you have a schema describing the XML or is it schema less?
Best regards
Michael
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uKZfcIWNHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I'm new on xml
> I have xml file in 500MB size, which i want to import it to table on sql
> server
> how can i do that?
>|||to one table
i have one file without schema with data
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eE7romhNHHA.5000@.TK2MSFTNGP03.phx.gbl...
> How exactly do you want to get it into a table? As an XML datatype
> instance in a row? Or are you planning on shredding information into one
> or multiple tables? Do you have a schema describing the XML or is it
> schema less?
> Best regards
> Michael
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uKZfcIWNHHA.1248@.TK2MSFTNGP02.phx.gbl...
>|||I would look at creating an annotated schema for the data and use the SQL
XML bulkload if you have to perform this several times. If you have to do it
once, look into OpenXML (in SQL Server 2000) or the nodes() method (in SQL
Server 2005),
More information about any of these three approaches can be found in the
archives or Books Online.
Best regards
Michael
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%235t48EXOHHA.3944@.TK2MSFTNGP06.phx.gbl...
> to one table
> i have one file without schema with data
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eE7romhNHHA.5000@.TK2MSFTNGP03.phx.gbl...
>

importing xml file

Hello there
I'm new on xml
I have xml file in 500MB size, which i want to import it to table on sql
server
how can i do that?
How exactly do you want to get it into a table? As an XML datatype instance
in a row? Or are you planning on shredding information into one or multiple
tables? Do you have a schema describing the XML or is it schema less?
Best regards
Michael
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:uKZfcIWNHHA.1248@.TK2MSFTNGP02.phx.gbl...
> Hello there
> I'm new on xml
> I have xml file in 500MB size, which i want to import it to table on sql
> server
> how can i do that?
>
|||to one table
i have one file without schema with data
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:eE7romhNHHA.5000@.TK2MSFTNGP03.phx.gbl...
> How exactly do you want to get it into a table? As an XML datatype
> instance in a row? Or are you planning on shredding information into one
> or multiple tables? Do you have a schema describing the XML or is it
> schema less?
> Best regards
> Michael
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:uKZfcIWNHHA.1248@.TK2MSFTNGP02.phx.gbl...
>
|||I would look at creating an annotated schema for the data and use the SQL
XML bulkload if you have to perform this several times. If you have to do it
once, look into OpenXML (in SQL Server 2000) or the nodes() method (in SQL
Server 2005),
More information about any of these three approaches can be found in the
archives or Books Online.
Best regards
Michael
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%235t48EXOHHA.3944@.TK2MSFTNGP06.phx.gbl...
> to one table
> i have one file without schema with data
> "Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
> news:eE7romhNHHA.5000@.TK2MSFTNGP03.phx.gbl...
>

Importing xml document to sql 2000 server using xsd schema.

Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.
In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegr oups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.
|||Actually, almost right, it should be sql:datatype. e.g.
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:datatype="nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ey7ARQBPFHA.3788@.tk2msftngp13.phx.gbl...
In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegr oups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.

Importing xml document to sql 2000 server using xsd schema.

Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegroups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.|||Actually, almost right, it should be sql:datatype. e.g.
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:datatype="nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"Graeme Malcolm" <graemem_cm@.hotmail.com> wrote in message
news:ey7ARQBPFHA.3788@.tk2msftngp13.phx.gbl...
In the XSD schema, the second "type" attribute should be "sql:type", and you
need to use "=" instead of ":" to specify the value.
e.g. instead of:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
use:
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" sql:type="sql:nvarchar(9)"/>
--
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
"SESC-SQLDeveloper@.telus.net" <itimilsina@.savannaenergy.com> wrote in
message news:1112893150.970921.243090@.o13g2000cwo.googlegroups.com...
Hi there,
I am trying to import xml documet to sql(2000) table using bulkload and
xsd schma, some how its not working. i change the schema and make is
XRD WITH THE SAME XML DOCUMENT ITS WORK BUT ITS DOESNOT WORK WITH XSD.
I install sqlxml 3.0 in the server as well as in my computer. Does any
one know where is the error.
below is the xsd shema.
==========
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="ExportTourComments"
sql:relation="TourCommentsField">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="TourSheetNumber" sql:field="TourSheetNumber"
type="xsd:string" type:"sql:nvarchar(9)"/>
<xsd:element name="TourCommentOrder" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentFrom" sql:field="TourSheetNumber"
type="xsd:DateTime" type:"sql:DateTime"/>
<xsd:element name="TourCommentTo" sql:field="TourSheetNumber"
type="xsd:DateTime"type:"sql:DateTime"/>
<xsd:element name="TourCommentDescription"
sql:field="TourSheetNumber" type="xsd:string" type:"sql:nvarchar(95)"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
====
WHEN I RUN THE VBS, it give error message:
script: name.vbs
line:5
char:1
error: A name contained as invalid character
code: 80004005
source: Schema mapping
=========
XRD SCHEMA, ITS WORKING
=========
<?xml version="1.0"?>
<Schema xmlns="urn:schemas-microsoft-com:xml-data"
xmlns:dt="urn:schemas-microsoft-com:xml:datatypes"
xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<ElementType name="TourSheetNumber" dt:type="string"/>
<ElementType name="TourCommentOrder" dt:type="Date"
sql:datatype="dateTime" />
<ElementType name="TourCommentFrom" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentTo" dt:type="Date"
sql:datatype="dateTime"/>
<ElementType name="TourCommentDescription" dt:type="string"/>
<ElementType name="dataroot" sql:is-constant="1">
<element type ="ExportTourComments"/>
</ElementType>
<ElementType name="ExportTourComments"
sql:relation="TourCommentsField">
<element type="TourSheetNumber" sql:field="TourSheetNumber" />
<element type="TourCommentOrder" sql:field="TourCommentOrder"
/>
<element type="TourCommentFrom" sql:field="TourCommentFrom" />
<element type="TourCommentTo" sql:field="TourCommentTo" />
<element type="TourCommentDescription"
sql:field="TourCommentDescription" />
</ElementType>
</Schema>
XML DAT
======
<?xml version="1.0" encoding="UTF-16"?>
<dataroot>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:44:16</TourCommentOrder>
<TourCommentDescription>over to the next location and cleaned the snow
from around the well. Calgery decided to</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:43:10</TourCommentOrder>
<TourCommentDescription>foggy. Have to wait untill 13:00 to find out if
we can move. While we were waiting we went</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:37:27</TourCommentOrder>
<TourCommentDescription>stay here and rig up. Rig up all equipment to
regulations. Hold safety meeting. Rig up</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 14:38:49</TourCommentOrder>
<TourCommentTo>1899-12-30 17:30:00</TourCommentTo>
<TourCommentDescription>slickline. Run recorders. Rig out slickline.
Turn well over to the testers. S.D.F.N.</TourCommentDescription>
</ExportTourComments>
<ExportTourComments>
<TourSheetNumber>010501181</TourSheetNumber>
<TourCommentOrder>2005-01-18 12:42:11</TourCommentOrder>
<TourCommentFrom>1899-12-30 09:30:00</TourCommentFrom>
<TourCommentDescription>Service and start equipment. Hold pre-job
safety meeting. Could not get permits. Too icy
and</TourCommentDescription>
</ExportTourComments>
</dataroot>
Thanks.sql

importing xml document to multiple related table with identity column.

Hi,
I am new to XML and looking for some information and suggestion. I need
to import xml document into related table having identity column, could
any one let me know which is the best way to do this. detail
information:
suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
bring information from these xml document to 3 different table whcih
are related with identity key (primary identity key).
table A: has identity key (A1)which is also a primary key with other
field.
table B: has identity key (B1)which is a primany key, column A1 which
is FK and other field
table C: has identity key (C1) which is a primary key, Column A1 wihic
is FK and other field.
xml document a.xml contain the information or record for table A, b.xml
contain for table B and c.xml contain for table C.
First i would like to bring the information from a.xml to the table A,
withour identity key from xml, it will be generated to the sql server
(this is only one row of data), the identity generated with be the max
of identity. I will like to bring this max of identity or identity
field just generated along with othere infromation from b.xml to table
B, similar to table C.
Could any one let me konw which is the best way to solve this problem.
Thanks in advance.
Indra.Can you post DDL (CREATE TABLE statements) for your tables, an example of
the XML document you're trying to import, and a list of which fields from
the XML correspond to which columns in the tables?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegroups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>|||You can create a stored procedure which takes these XMLs as parameters
nText.
Load the XMLs into XMLDOcuments using sp_xml_preparedocument to read xml
contents.
you can check how to use this extended stored procedure here..
http://msdn.microsoft.com/library/d...r />
_267o.asp
First prepare the insert statement to Insert Into Table A. Get identity
column with Select Scope_Identity()
use this to insert into Table B and Table C by preparing the insert
statements using XML B and C.
you will have to loop through the records. The best way would be to Insert
all records into Table A. Then write statements insert into
TabeB and C. it can be done simply with just 3 insert statements. but you
need to map tTable A records with B and C.
--
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegroups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>|||"avnrao" <avn@.newsgroups.com> wrote in message
news:uTIecHT$EHA.612@.TK2MSFTNGP09.phx.gbl...
> First prepare the insert statement to Insert Into Table A. Get identity
> column with Select Scope_Identity()
> use this to insert into Table B and Table C by preparing the insert
> statements using XML B and C.
> you will have to loop through the records. The best way would be to Insert
> all records into Table A. Then write statements insert into
> TabeB and C. it can be done simply with just 3 insert statements. but you
> need to map tTable A records with B and C.
If there are multiple rows inserted, SCOPE_IDENTITY() will not do the
job. And there is no reason to do a loop of any kind. The entire thing
should be able to be done in a set-based manner.|||Hit send too soon on the last message.
Something like this... no loops necessary:
use tempdb
go
create table abc(somedata char(10), abcID int identity(1,1) primary key)
go
create table def(somedata char(10), abcID int references abc(abcid))
go
declare @.xmldoc varchar(8000)
set @.xmldoc =
'<anode>
<another somedata="something">
<blah someother="else"/>
</another>
<another somedata="something2">
<blah someother="else2"/>
</another>
</anode>'
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmldoc
insert abc (somedata)
SELECT somedata
FROM OPENXML (@.hdoc, '/anode/another')
with (somedata char(10) '@.somedata')
insert def (somedata, abcid)
SELECT x.someother, abc.abcid
FROM
(SELECT somedata, someother
FROM OPENXML (@.hdoc, '/anode/another')
with (
somedata char(10) '@.somedata',
someother char(10) 'blah/@.someother')) x (somedata, someother)
JOIN abc ON abc.somedata = x.somedata
EXEC sp_xml_removedocument @.hdoc
go
select *
from abc
go
select *
from def
go
drop table def
drop table abc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

importing xml document to multiple related table with identity column.

Hi,
I am new to XML and looking for some information and suggestion. I need
to import xml document into related table having identity column, could
any one let me know which is the best way to do this. detail
information:
suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
bring information from these xml document to 3 different table whcih
are related with identity key (primary identity key).
table A: has identity key (A1)which is also a primary key with other
field.
table B: has identity key (B1)which is a primany key, column A1 which
is FK and other field
table C: has identity key (C1) which is a primary key, Column A1 wihic
is FK and other field.
xml document a.xml contain the information or record for table A, b.xml
contain for table B and c.xml contain for table C.
First i would like to bring the information from a.xml to the table A,
withour identity key from xml, it will be generated to the sql server
(this is only one row of data), the identity generated with be the max
of identity. I will like to bring this max of identity or identity
field just generated along with othere infromation from b.xml to table
B, similar to table C.
Could any one let me konw which is the best way to solve this problem.
Thanks in advance.
Indra.
Can you post DDL (CREATE TABLE statements) for your tables, an example of
the XML document you're trying to import, and a list of which fields from
the XML correspond to which columns in the tables?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegr oups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>
|||You can create a stored procedure which takes these XMLs as parameters
nText.
Load the XMLs into XMLDOcuments using sp_xml_preparedocument to read xml
contents.
you can check how to use this extended stored procedure here..
http://msdn.microsoft.com/library/de...p_xml_267o.asp
First prepare the insert statement to Insert Into Table A. Get identity
column with Select Scope_Identity()
use this to insert into Table B and Table C by preparing the insert
statements using XML B and C.
you will have to loop through the records. The best way would be to Insert
all records into Table A. Then write statements insert into
TabeB and C. it can be done simply with just 3 insert statements. but you
need to map tTable A records with B and C.
Av.
http://dotnetjunkies.com/WebLog/avnrao
http://www28.brinkster.com/avdotnet
<itimilsina@.savannaenergy.com> wrote in message
news:1105992192.804216.302410@.z14g2000cwz.googlegr oups.com...
> Hi,
> I am new to XML and looking for some information and suggestion. I need
> to import xml document into related table having identity column, could
> any one let me know which is the best way to do this. detail
> information:
> suppose i have 3 xml document call a.xml, b.xml and c.xml. I need to
> bring information from these xml document to 3 different table whcih
> are related with identity key (primary identity key).
> table A: has identity key (A1)which is also a primary key with other
> field.
> table B: has identity key (B1)which is a primany key, column A1 which
> is FK and other field
> table C: has identity key (C1) which is a primary key, Column A1 wihic
> is FK and other field.
> xml document a.xml contain the information or record for table A, b.xml
> contain for table B and c.xml contain for table C.
> First i would like to bring the information from a.xml to the table A,
> withour identity key from xml, it will be generated to the sql server
> (this is only one row of data), the identity generated with be the max
> of identity. I will like to bring this max of identity or identity
> field just generated along with othere infromation from b.xml to table
> B, similar to table C.
> Could any one let me konw which is the best way to solve this problem.
> Thanks in advance.
> Indra.
>
|||"avnrao" <avn@.newsgroups.com> wrote in message
news:uTIecHT$EHA.612@.TK2MSFTNGP09.phx.gbl...
> First prepare the insert statement to Insert Into Table A. Get identity
> column with Select Scope_Identity()
> use this to insert into Table B and Table C by preparing the insert
> statements using XML B and C.
> you will have to loop through the records. The best way would be to Insert
> all records into Table A. Then write statements insert into
> TabeB and C. it can be done simply with just 3 insert statements. but you
> need to map tTable A records with B and C.
If there are multiple rows inserted, SCOPE_IDENTITY() will not do the
job. And there is no reason to do a loop of any kind. The entire thing
should be able to be done in a set-based manner.
|||Hit send too soon on the last message.
Something like this... no loops necessary:
use tempdb
go
create table abc(somedata char(10), abcID int identity(1,1) primary key)
go
create table def(somedata char(10), abcID int references abc(abcid))
go
declare @.xmldoc varchar(8000)
set @.xmldoc =
'<anode>
<another somedata="something">
<blah someother="else"/>
</another>
<another somedata="something2">
<blah someother="else2"/>
</another>
</anode>'
DECLARE @.hdoc int
EXEC sp_xml_preparedocument @.hdoc OUTPUT, @.xmldoc
insert abc (somedata)
SELECT somedata
FROM OPENXML (@.hdoc, '/anode/another')
with (somedata char(10) '@.somedata')
insert def (somedata, abcid)
SELECT x.someother, abc.abcid
FROM
(SELECT somedata, someother
FROM OPENXML (@.hdoc, '/anode/another')
with (
somedata char(10) '@.somedata',
someother char(10) 'blah/@.someother')) x (somedata, someother)
JOIN abc ON abc.somedata = x.somedata
EXEC sp_xml_removedocument @.hdoc
go
select *
from abc
go
select *
from def
go
drop table def
drop table abc
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic

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

Wednesday, March 28, 2012

Importing users from SQL to NDS LDIF

hi all,
i'm trying to figure out how to import users from SQL table to netscape directory server (if it's even possible). i know you can do it by using third party tools like ldifde to import from SQL to AD, but i don't think it will wrok for SQL to NDS....

can anyone please help??

thanks
AlexDo you have the import specifications for ldif ? If so, you should be able to export the sql server data in that format.|||Originally posted by rnealejr
Do you have the import specifications for ldif ? If so, you should be able to export the sql server data in that format.

I do have specifications for LDIF, but i'm still unsure how to export SQL data in that format... would you please give me some more info?

Thanks much!

Alex

importing txt file to multiple table in sql 2000

Hi There,

I am looking for information on how to import the txt or csv file to
the multiple table in sql 2000. If you have any kind of inf. please
let me know wheather we can do this and how.

below is the detail information.

I received txt file every day which contain the information from 3
different related table in my sql 2000 database. Right now we are
keyin the information from the web site (which is link to the txt
file) to our database, but i am wondering if we can import those
record in the tables.

the header of the file goes to table1 and when we insert the record in
table1, it should generate the autoidentityrecord (PK), and that PK is
link to other table2 and table3 where rest of the information from txt
file goes. For table2 and table3 there are multiple record per txt
files.

in our txt file each row is separated with row header, like HTC100
WITH ROW NO. 1,2,3.., which indecate this information goes to table
and 1,2...are the different row number.

Please let me know whether we can achive this task or not.

Thanks for all you help in advance.

Indra.

I have pasted my txt file below:
========

"FHS000",20041029,0900,,"10",1,"TRAILB10_20041029_1B",3,"2.20","Pason-DataHub",,"#
Well 1098831406 Tour 2004/10/29 Trailblazer 10 148",1,"EDR_3-0-10_HF2
ETS 2.2"
"CON000",1,0000,0759
"CON000",2,0800,1559
"CON000",3,1600,2359
"HWI010","0312857","COMPTON BRANT 15-7-18-24","COMPTON PETROLEUM
CORP.","TRAILBLAZER DRILLING
CORP.","15-07-018-24W4","100/15-07-018-24W4/00","HANK
PARANYCH","CURTIS FIESEL",20041029,,,"10",20041027,0600,,,"148","DD04485","VERT.","NO",,
"HCO030",1,"Daily Walk Around Inspection","HP","CF"
"HCO030",2,"Detailed Inspection - Weekly (using checklist)","HP","CF"
"HCO030",3,"H2S Signs Posted (if required)",,
"HCO030",4,"Well License & Stick Diagram Posted","HP","CF"
"HCO030",5,"Flare Lines Staked","HP","CF"
"HCO030",6,"BOP Drills Performed","HP","CF"
"HCO030",7,"Visually Inspect BOP's - Flarelines and Degasser
Lines","HP","CF"
"HDC040",1,"Rig Site Health and Safety Meeting (one/crew/month)","CF"
"HDC040",2,"C.A.O.D.C. Rig Safety Inspection Checklist
(one/rig/month)","CF"
"HDC040",3,"Mast Inspection Before Raising or Lowering","CF"
"HDC040",4,"Crown Saver Checked","CF"
"HDC040",5,"Motor Kills Checked","CF"
"HFU050",2300,2100,,
"HWE060",-5,"Deg C","COOL","WEST","SLIPPERY",,
"HCS070",1,177.8,,"mm",25.3,"STELCO","J-55",8,108.44,3.84,108.44,
"HCS070",2,114.3,,"mm",14.14,"STELCO","J-55",72,979.50,3.84,979.0,
"HDP080",1,127,79.4,"kg/m","E",57,127,"mm","3 1/2 IF",10,"DC",
"HDP080",2,89,19.7,"kg/m","E",68,120,"mm","3 1/2 IF",15,"DP",
"HPU090",1,"F-800","EMSCO",254,"mm",,,,
"HPU090",2,"F-800","EMSCO",254,"mm",,,,
"HTC100",1,"Rig up and tear down"
"HTC100",2,"Drill Actual"
"HTC100",3,"Reaming"
"HTC100",4,"Coring"
"HTC100",5,"Condition Mud & Circulate"
"HTC100",6,"Trips"
"HTC100",7,"Rig Service"
"HTC100",8,"Repair Rig"
"HTC100",9,"Cut off drilling line"
"HTC100",10,"Deviation Survey"
"HTC100",11,"Wire Line Logs"
"HTC100",12,"Run Case & Cement"
"HTC100",13,"Wait on Cement"
"HTC100",14,"Nipple up B.O.P."
"HTC100",15,"Test B.O.P."
"HTC100",16,"Drill Stem Test"
"HTC100",17,"Plug Back"
"HTC100",18,"Squeeze Cement"
"HTC100",19,"Fishing"
"HTC100",20,"Directional Work"
"HTC100",21,"Safety Meeting"
"HTC100",24,"WOD"
"HSS110",1,1,"SWACO","N","110",,"84",,
"HPA130","COMPTON BRANT 15-7-18-24",20041029,"COMPTON PETROLEUM
CORP.","TRAILBLAZER DRILLING CORP.","CURTIS
FIESEL","10","ALBERTA","N",253
"TCP130",1,,,,"kPa",140,,,,"mm",,
"TCP130",2,,,,"kPa",140,,,,"mm",,
"TCP130",3,,,,"kPa",140,,,,"mm",,
"TTL160",1,1,0.00,0.25,0.25,21,"SAFETY MEETING WITH TONG HAND"
"TTL160",1,2,0.25,1.75,1.50,12,"RIG TO AND RUN CASING"
"TTL160",1,3,1.75,2.00,0.25,7,"RIG SERVICE"
"TTL160",1,4,2.00,2.50,0.50,5,"CONDITION MUD & CIRC."
"TTL160",1,5,2.50,2.75,0.25,21,"SAFETY MEETING WITH CEMENTERS"
"TTL160",1,6,2.75,3.50,0.75,12,"RIG TO AND CEMENT CASING"
"TTL160",1,7,3.50,6.00,2.50,1,"SET SLIPS, TEAR OUT RIG, CLEAN TANKS"
"TTL160",1,8,6.00,8.00,2.00,24,"WAIT ON DAYLIGHT/TRUCKS"
"TTL160",1,9,,,,,"CEMENT WITH BJ USING 13 TONNES OF BVF-1500 NP + .7%
FL-5,GIVING 15.5 m3 OF GOOD"
"TTL160",1,10,,,,,"SLURRY @. 1718 kg/m3,PLUG BUMPED & HELD @. 03:30 HRS
OCT 29/04."
"TTL160",1,11,,,,,"RIG RELEASED @. 08:00 HRS OCT 29/04"
"TTL160",1,12,,,,,"MOVE TO 12-3-18-25W4"
"TDI170",1,"JEFF CASE",8,10,475,"Deg C",,,"RUNNING CASING",,,,,
"TLN175",1,"VISUALLY INSPECT PINS, RAMS AND STOOLS PRIOR TO LAYING
OVER DERRICK"
"TPA180",1,1,"DRILLER",647172865,"JEFF CASE",8,,,"JC"
"TPA180",1,2,"DERRICK HAND",648519056,"BRYAN VANHAM",8,,,"BV"
"TPA180",1,3,"MOTOR HAND",651056533,"NEIL WILLIAMS",8,,,"NW"
"TPA180",1,4,"FLOOR HAND",640352662,"TARAS WOITAS",8,,,"TW"
"TPI190",1,"REG",25,,,,,,
"TPI190",2,"REG",25,,,,,,
"TPI190",3,"REG",25,,,,,,
=====[posted and mailed, please reply in news]

Indra (itimilsina@.savannaenergy.com) writes:
> I am looking for information on how to import the txt or csv file to
> the multiple table in sql 2000. If you have any kind of inf. please
> let me know wheather we can do this and how.
> below is the detail information.
> I received txt file every day which contain the information from 3
> different related table in my sql 2000 database. Right now we are
> keyin the information from the web site (which is link to the txt
> file) to our database, but i am wondering if we can import those
> record in the tables.
> the header of the file goes to table1 and when we insert the record in
> table1, it should generate the autoidentityrecord (PK), and that PK is
> link to other table2 and table3 where rest of the information from txt
> file goes. For table2 and table3 there are multiple record per txt
> files.
> in our txt file each row is separated with row header, like HTC100
> WITH ROW NO. 1,2,3.., which indecate this information goes to table
> and 1,2...are the different row number.
> Please let me know whether we can achive this task or not.

Of course, it is possible. However, as far as I can see only by means of
writing a program that reads and parses the file. The standard tools for
loading files into SQL Server are BCP and DTS. BCP cannot cope with your
file, because there is a mix of record formats. BCP can only import files
with uniform records.

DTS is more versatile than BCP, but I as far as can see, you will still
have to write code to have DTS to import the file. I need to add the
disclaimer that I have zero experience of DTS.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I agree with Erland. Could it be done with BCP and TSQL? Yes. Would it be
an efficient, maintainable, error resilient code? No.

It would be far better to write a piece of code or better get the sender to
supply X number of files, one for each table. The original creator of the
file obviously has control over the file format.

Danny

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns95998D7498D77Yazorman@.127.0.0.1...
> [posted and mailed, please reply in news]
> Indra (itimilsina@.savannaenergy.com) writes:
>> I am looking for information on how to import the txt or csv file to
>> the multiple table in sql 2000. If you have any kind of inf. please
>> let me know wheather we can do this and how.
>>
>> below is the detail information.
>>
>> I received txt file every day which contain the information from 3
>> different related table in my sql 2000 database. Right now we are
>> keyin the information from the web site (which is link to the txt
>> file) to our database, but i am wondering if we can import those
>> record in the tables.
>>
>> the header of the file goes to table1 and when we insert the record in
>> table1, it should generate the autoidentityrecord (PK), and that PK is
>> link to other table2 and table3 where rest of the information from txt
>> file goes. For table2 and table3 there are multiple record per txt
>> files.
>>
>> in our txt file each row is separated with row header, like HTC100
>> WITH ROW NO. 1,2,3.., which indecate this information goes to table
>> and 1,2...are the different row number.
>>
>> Please let me know whether we can achive this task or not.
> Of course, it is possible. However, as far as I can see only by means of
> writing a program that reads and parses the file. The standard tools for
> loading files into SQL Server are BCP and DTS. BCP cannot cope with your
> file, because there is a mix of record formats. BCP can only import files
> with uniform records.
> DTS is more versatile than BCP, but I as far as can see, you will still
> have to write code to have DTS to import the file. I need to add the
> disclaimer that I have zero experience of DTS.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Danny (istdrs@.flash.net) writes:
> I agree with Erland. Could it be done with BCP and TSQL? Yes.

No. :-) Since there is a variable number of fields on each row, BCP would
be lost.

> It would be far better to write a piece of code or better get the sender
> to supply X number of files, one for each table. The original creator
> of the file obviously has control over the file format.

Yes, fixing the file format may very well be a good idea.

Interesting enough, it could still be one file, if the shorter rows were
padded with extra fields. Note that just adding ,,,,, would not be enough,
since the " too are delimiters as far as BCP is concerned, so the usage of
" must be consistent from record to record. (Which does not seem to be the
case in the current file.)
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland,

I am still trying to solve this problem of inserting txt file and
manupulating data to insert in differet table in sql 2000. I have
problem of inserting nvarchar which is coming from txt file to datetime
field in sql table.

If you look through my txt file there is a row start with TTL160 AND WE
HAVE COL4 AND COL5 WITH DATA LIKE 1.00 AND 12.23 ETC,(WHICH IS IN
NVARCHAR), I NEED TO INSERT THIS VALUE AS 1:00 OR 12:23 IN MY ANOTHER
TABEL IN DATETIME FIEDL. I COULD USED REPLACE(COLUMN, '.',':') TO
REPLACE FROM 12.23 TO 12:23 BUT WHEN I INSERT THIS TO SQL DATETIME FIELD
ITS GIVING ME ERROR " Arithmetic overflow error converting expression
to data type datetime".I try to use Cast function, still same error,
could MICROSOFT have a look please.

Thanks for help.

Indra.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

importing trace data into table...

I follow the instructions to import text data from the link below:
http://support.microsoft.com/default...b;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?
Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/default...b;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
David Gugick
Imceda Software
www.imceda.com
|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP10.phx.gbl>...
> Tascien wrote:
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
sql

importing trace data into table...

I follow the instructions to import text data from the link below:
http://support.microsoft.com/defaul...kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/defaul...kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP
10.phx.gbl>...
> Tascien wrote:
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?

importing trace data into table...

I follow the instructions to import text data from the link below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
--
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP10.phx.gbl>...
> Tascien wrote:
> > I follow the instructions to import text data from the link below:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> >
> > but when i look at the trace_table, in the columb textdata, it's
> > NULL... where can i find the content?
> >
> > Infact, the only columns that have data, is SPID, servername,
> > eventclass all other columns are NULL...
> >
> > any help?
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?

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 Text File: How to dynamically change the row delimiter

Hi,

I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

Any help would be appreciated

I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function

I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:

type inputfile.txt>newfile.txt

before importing. Type may convert LF to CRLF.

I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Importing TEXT File in DTS

Hello Guys,
I Hava a Source text connection and I'd like to take just the first row ( the header, of course) of the file to one table. How can I get this??
Tis is quite Urgent.
Thanxs;Does it have to be DTS?

Why not DTS in to a single column table (varchar(8000)) and the parse out the data in to the final table?|||Because the text files can be larger than 200MB. :(
I need to take just the first Row of the text file to know some important informations.|||You sure you're talking about row size?

That's a long row....|||No. Im speaking about the File.

Look an example of the beggining of the file:
I want to get the first row of the file and put it into a column. Note, just the first row. You can see that the another lines are in a different layout and would make my table very big.

A221539 DPVAT - COD BAR 151BANCO NOSSA
G00000000000000 20040123200401298664000000093373362
G00000000000000 20040123200401298663000000093383362
G00000000000000 20040123200401298669000000051623362
G00000000000000 20040123200401298669000000093383362
G00000000000000 20040123200401298664000000093383362
G00000000000000 20040123200401298661000000093383362
G00000000000000 20040123200401298661000000055433362|||Have you looked at BULK INSERT in BOL?

You can specify first row and last row (ie 1 and 1)

Importing text file

Hi all,
I'm importing a text-file into a SQL table, The catch is, I want to run a UDF on one of the columns before it gets added to the table... I would like to do the importing, and conversion in one step, and i'd prefer to do this using bulk insert, or bcp if I
have to, as this will run frequently on the data. ANY help will be greatly appreciated.
Rival
Your best bet would be to use SQL Server Data Transforation Services (DTS),
as you would be able to perform the transformations required and import all
in one step. For more information refer to Data Transformation Services in
SQL Server Books Online (BOL). A good DTS reference site is www.sqldts.com.
--
PETER WARD
WARDY Inc.
www.wardyinc.com
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.
|||Rival,
Bear in mind that some UDFs will not be set based, ie they will operate row
by row and will be poor performers. Look into DTS, or bcp into a staging
table and then run a single INSERT...SELECT statement to your destination.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.
sql

Importing text file

Hi all
I'm importing a text-file into a SQL table, The catch is, I want to run a UDF on one of the columns before it gets added to the table... I would like to do the importing, and conversion in one step, and i'd prefer to do this using bulk insert, or bcp if I have to, as this will run frequently on the data. ANY help will be greatly appreciated.Rival
Your best bet would be to use SQL Server Data Transforation Services (DTS),
as you would be able to perform the transformations required and import all
in one step. For more information refer to Data Transformation Services in
SQL Server Books Online (BOL). A good DTS reference site is www.sqldts.com.
--
--
PETER WARD
WARDY Inc.
www.wardyinc.com
--
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.|||Rival,
Bear in mind that some UDFs will not be set based, ie they will operate row
by row and will be poor performers. Look into DTS, or bcp into a staging
table and then run a single INSERT...SELECT statement to your destination.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.

Importing text file

Hi all,
I'm importing a text-file into a SQL table, The catch is, I want to run a UD
F on one of the columns before it gets added to the table... I would like to
do the importing, and conversion in one step, and i'd prefer to do this usi
ng bulk insert, or bcp if I
have to, as this will run frequently on the data. ANY help will be greatly a
ppreciated.Rival
Your best bet would be to use SQL Server Data Transforation Services (DTS),
as you would be able to perform the transformations required and import all
in one step. For more information refer to Data Transformation Services in
SQL Server Books Online (BOL). A good DTS reference site is www.sqldts.com.
--
PETER WARD
WARDY Inc.
www.wardyinc.com
--
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.|||Rival,
Bear in mind that some UDFs will not be set based, ie they will operate row
by row and will be poor performers. Look into DTS, or bcp into a staging
table and then run a single INSERT...SELECT statement to your destination.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
"Rival" <anonymous@.discussions.microsoft.com> wrote in message
news:5D60CE30-BFA2-4F13-BE8D-64059DE91D82@.microsoft.com...
> Hi all,
> I'm importing a text-file into a SQL table, The catch is, I want to run a
UDF on one of the columns before it gets added to the table... I would like
to do the importing, and conversion in one step, and i'd prefer to do this
using bulk insert, or bcp if I have to, as this will run frequently on the
data. ANY help will be greatly appreciated.

Friday, March 30, 2012

imports table structures in SQL 2000 into Excel

Importing XML into SQL using DTS

importing xml file

importing xml file

Importing xml document to sql 2000 server using xsd schema.

Importing xml document to sql 2000 server using xsd schema.

importing xml document to multiple related table with identity column.

importing xml document to multiple related table with identity column.

importing xml

Wednesday, March 28, 2012

Importing users from SQL to NDS LDIF

importing txt file to multiple table in sql 2000

importing trace data into table...

importing trace data into table...

importing trace data into table...

Importing textfiles in to text fields how ?

Importing Text File: How to dynamically change the row delimiter

Importing TEXT File in DTS

Importing text file

Importing text file

Importing text file

Importing Access DB

Blog Archive

About Me