Imporving Performence

Can any body tell me what measures I have to take to improve the performance of my SQL QUERIES and Stored procedures any thing relatted to SQL?Lookup "Query Tuning" in Books Online.

blindman|||We can definitely identify optimization opportunities for a specific query, but for all of your queries you either have to do it yourself or hire a consultant to do it for you.

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

imports microsoft.sqlserver.dts.pipeline does not work

I have been trying to follow/implement the examples in the following help topics (thanks to Jamie for these links).

Building Packages Programmatically

(http://msdn2.microsoft.com/en-us/library/ms345167.aspx)

Connecting Data Flow Components Programmatically

(http://msdn2.microsoft.com/en-us/library/ms136086.aspx)

The problem I am having is that MainPipe is not recognized as a valid type in my Script task, even though I have the imports statements that are listed in the example. I get the message "Error 30002: Type 'MainPipe' is not defined". The other and related problem is that when I type "imports microsoft.sqlserver.dts", the intellisense offers only two choices: {}Runtime and {}Tasks. I don't see any choice for Pipeline. Can anyone tell what I am missing? It seems to be some kind of configuration/installation issue, but I have no idea how to resolve it. I have tried this on 3 different machines, with both the RTM SQL 2005 standard edition, and with SP2 installed, all with the same result. Any help is appreciated

Here is my code:

' Microsoft SQL Server Integration Services Script Task

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.SqlServer.Dts.Pipeline

Imports Microsoft.SqlServer.Dts.Pipeline.wrapper

Imports Microsoft.SqlServer.Dts.

Public Class ScriptMain

Public Sub Main()

'

Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _

New Microsoft.SqlServer.Dts.Runtime.Package()

Dim e As Executable = package.Executables.Add("DTS.Pipeline.1")

Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _

CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)

Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)

Dts.TaskResult = Dts.Results.Success

End Sub

End Class

Make sure you have added a reference (under Project..References) to the Microsoft.SqlServer.DTSPipelineWrap.dll.

|||That was it. Thanks for your help.

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 to SQL Server: National "Do Not Call" database

I need to import the "National Do Not Call" database into SQL server.
It is in XML format (*.XML) and I understand it consists of 2 fields: area
code and phone number and about 50 million records.
What is the best way to get this file into a SQL Server table?One option would be to look at using SQLXML and XML Bulk
Load. You can find more information, links and a download
link at:
http://msdn.microsoft.com/nhp/Default.asp?contentid=28001300
-Sue
On Thu, 18 Sep 2003 16:56:48 -0700, "DaveF"
<davef@.comcast.net> wrote:
>I need to import the "National Do Not Call" database into SQL server.
>It is in XML format (*.XML) and I understand it consists of 2 fields: area
>code and phone number and about 50 million records.
>What is the best way to get this file into a SQL Server table?
>

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 Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server
.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a wly feed from this vendor and will
need to import on a schedule. Thanks.Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a wly feed from this vendor and
> will
> need to import on a schedule. Thanks.|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
>|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAx
apta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotation
s
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT paramete
r
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutions
Axapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
>

Importing XML Sample file and XML Schema

I recently received a schema file from a vendor to import into my SQL Server.
I am new to XML and have been reading all I can. I tried doing a Bulk Load
in a DTS Package, but it does not read the schema as the correct file format
(asking me ANSI, Unicode, etc). I have a sample data file to test with but
it does not seem to be working. Can anyone give any insight on the easiest
way to do this? I will be receiving a weekly feed from this vendor and will
need to import on a schedule. Thanks.
Do you need to "shred" it into relational fields or keep the whole XML
document in a single field?
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>I recently received a schema file from a vendor to import into my SQL
>Server.
> I am new to XML and have been reading all I can. I tried doing a Bulk
> Load
> in a DTS Package, but it does not read the schema as the correct file
> format
> (asking me ANSI, Unicode, etc). I have a sample data file to test with
> but
> it does not seem to be working. Can anyone give any insight on the
> easiest
> way to do this? I will be receiving a weekly feed from this vendor and
> will
> need to import on a schedule. Thanks.
|||I need to shred it. There are multiple tables involved.
"Michael Rys [MSFT]" wrote:

> Do you need to "shred" it into relational fields or keep the whole XML
> document in a single field?
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:51CB2EE7-C1F2-4F3E-84FB-7BF425C154F2@.microsoft.com...
>
>
|||You have a couple of options:
Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
to your schema (see the documentation).
Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
(you need to make sure that the XML document is in an encoding compatible
with the SQL Server collation codepage).
Write your own ASP/ASP.Net mid-tier code to perform the shredding.
Best regards
Michael
"Andi" <Andi@.discussions.microsoft.com> wrote in message
news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...[vbcol=seagreen]
>I need to shred it. There are multiple tables involved.
> "Michael Rys [MSFT]" wrote:
|||Hello - found the issue - apparently, the:
targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
needs to be removed from the <xs:schema> tag.
Now, can anyone tell me why? I'm not an XML expert but learning as much as I
can...
AB
"Michael Rys [MSFT]" wrote:

> You have a couple of options:
> Use the SQLXML 3.0 XML Bulkload component. You will need to add annotations
> to your schema (see the documentation).
> Use OpenXML. You pass the XML to a stored proc as a TEXT or NTEXT parameter
> (you need to make sure that the XML document is in an encoding compatible
> with the SQL Server collation codepage).
> Write your own ASP/ASP.Net mid-tier code to perform the shredding.
> Best regards
> Michael
> "Andi" <Andi@.discussions.microsoft.com> wrote in message
> news:FE4E6BEE-4BB2-452E-8A9B-BC9F0E20C3CE@.microsoft.com...
>
>
|||The targetnamespace indicates that the elements need to belong to the
namespace to be matched by the schema definitions.
Best regards
Michael
"Illustris" <Illustris@.discussions.microsoft.com> wrote in message
news:1EC9D995-DADE-4321-B9C4-AC8A55E07E51@.microsoft.com...[vbcol=seagreen]
> Hello - found the issue - apparently, the:
> targetNamespace="http://tempuri.org/fra-bel-xml-MicrosoftBusinessSolutionsAxapta30_2.xsd"
> needs to be removed from the <xs:schema> tag.
> Now, can anyone tell me why? I'm not an XML expert but learning as much as
> I
> can...
> AB
> "Michael Rys [MSFT]" wrote:
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 into sql server 7

Is it possible to import an XSD into SQL Server 7 and automagically create
the tables?
Cheers
Iain
None of the SQLXML features work with SQL Server 7. What you are asking for
is available with XML Bulk Load in SQL Server 2000.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:uK6FKAOpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Is it possible to import an XSD into SQL Server 7 and automagically create
> the tables?
> Cheers
> Iain
>
|||Thanks for the swift reply Roger.
Are there any 3rd party add-ons that you or anyone else know about that
would do the trick?
Here's the situation:
I have XSD documents describing my data (subject to design changes). I wish
to write ASP.NET pages to deal with my data and would prefer to treat it as
xml and use xslt rather than datasets and datagrids. The reason for this is
that my company has some rather funky generic client side scripts that
request the data through http as xml and dynamically build/rebuild the UI -
it saves quite a bit on server load.
However, I'm not convinced that storing the data as purely xml files at the
backend is a very safe or scalable option for a multiuser system. Therefore
I would like to store them in sql server. Also note that I don't have any
data yet as the app is still in design phase :-)
I can easily load them into an XMLDataDocument in .NET, which contains a
dataset object, so it would be relatively easy to use this to interface with
the db (I think that to do this I have to provide the sql to do this through
a data-adapter). However that still leaves the issue of creating the
tables... I either have to do this manually, or else 3rd party tools...
Am I going about this the right way?
Many thanks again
Iain
p.s. is SQLXml available through .NET platform APIs or is it SQL Server? I
know that it uses VB in some way, but I'm a bit hazy on how...
"Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
news:ej9oJVOpEHA.800@.TK2MSFTNGP14.phx.gbl...
> None of the SQLXML features work with SQL Server 7. What you are asking
for
> is available with XML Bulk Load in SQL Server 2000.
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm
> "Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
> news:uK6FKAOpEHA.592@.TK2MSFTNGP11.phx.gbl...
create
>
|||Bulkload offers SchemaGen option to create tables automatically on the
server based on your AXSD. You may use Bulkload in VB script, any language
supports COM or .Net languages via COM interrupts.
Bertan ARI
This posting is provided "AS IS" with no warranties, and confers no rights.
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:eOJl9jOpEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Thanks for the swift reply Roger.
> Are there any 3rd party add-ons that you or anyone else know about that
> would do the trick?
> Here's the situation:
> I have XSD documents describing my data (subject to design changes). I
wish
> to write ASP.NET pages to deal with my data and would prefer to treat it
as
> xml and use xslt rather than datasets and datagrids. The reason for this
is
> that my company has some rather funky generic client side scripts that
> request the data through http as xml and dynamically build/rebuild the
UI -
> it saves quite a bit on server load.
> However, I'm not convinced that storing the data as purely xml files at
the
> backend is a very safe or scalable option for a multiuser system.
Therefore
> I would like to store them in sql server. Also note that I don't have any
> data yet as the app is still in design phase :-)
> I can easily load them into an XMLDataDocument in .NET, which contains a
> dataset object, so it would be relatively easy to use this to interface
with
> the db (I think that to do this I have to provide the sql to do this
through
> a data-adapter). However that still leaves the issue of creating the
> tables... I either have to do this manually, or else 3rd party tools...
> Am I going about this the right way?
> Many thanks again
> Iain
> p.s. is SQLXml available through .NET platform APIs or is it SQL Server?
I
> know that it uses VB in some way, but I'm a bit hazy on how...
>
> "Roger Wolter[MSFT]" <rwolter@.online.microsoft.com> wrote in message
> news:ej9oJVOpEHA.800@.TK2MSFTNGP14.phx.gbl...
> for
> rights.
> create
>
|||Thanks for the reply Bertran...
Is bulkload not sql server 2000 only though?
I thought it was part of sqlxml.
Let me know if I'm wrong.
Cheers
Iain
"Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
news:%238Spb1ZpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> Bulkload offers SchemaGen option to create tables automatically on the
> server based on your AXSD. You may use Bulkload in VB script, any language
> supports COM or .Net languages via COM interrupts.
> --
> Bertan ARI
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
>
> "Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
> news:eOJl9jOpEHA.3252@.TK2MSFTNGP14.phx.gbl...
> wish
> as
this[vbcol=seagreen]
> is
> UI -
> the
> Therefore
any[vbcol=seagreen]
> with
> through
> I
asking
>
|||Yes. But the generated statements could be run on a 7.0 DB if needed.
XML Spy may be a non-Microsoft tool that may have such functionality (just a
guess).
Best regards
Michael
"Iain A. Mcleod" <mcleodia@.dcs.gla.ac.uk> wrote in message
news:u5woV8ZpEHA.2764@.TK2MSFTNGP11.phx.gbl...
> Thanks for the reply Bertran...
> Is bulkload not sql server 2000 only though?
> I thought it was part of sqlxml.
> Let me know if I'm wrong.
> Cheers
> Iain
> "Bertan ARI [MSFT]" <bertan@.online.microsoft.com> wrote in message
> news:%238Spb1ZpEHA.1296@.TK2MSFTNGP12.phx.gbl...
> rights.
> this
> any
> asking
>
sql

Importing XML in SQL Server 2005 in .Net 2.0

Yes, I have schema. I have also created database structure from XML
Schema. XML Schema is in such a way that it is not having annotation
suitlable for SQLXML.
You mentioned about loading XML into tables, but how do I update table
into database (I assume you mean Table datastruction in ADO.net)?
Thanks
NeoHello Neo,
Okay, so let me ask this: are you simply interested in loading the XML into
the database in some batch operation or do you both load the orginal data
into SQL Server and modify stored values from XML?
If it this is a simple ETL (former) case, SSIS would be my tool of choice.
If you're looking to both load and update from XML, SSIS is still usable,
but the solution might look a little different.
If you simply putting the XML into a XML-typed column, consider using openro
wset(bulk)
instead.
More details helpful. :)
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/

Importing XML in SQL Server 2000 in .Net 2.0

Hello Kent,
I realized, I am having SQL Server 2000 for this problem, and not 2005
(To many projects I guess). So, SSIS won't be possible. Same goes for
XML typed column.
Is there option for bulk upload in SQL Server 2000.
Kent Tegels wrote:
> Hello Neo,
> Okay, so let me ask this: are you simply interested in loading the XML int
o
> the database in some batch operation or do you both load the orginal data
> into SQL Server and modify stored values from XML?
> If it this is a simple ETL (former) case, SSIS would be my tool of choice.
> If you're looking to both load and update from XML, SSIS is still usable,
> but the solution might look a little different.
> If you simply putting the XML into a XML-typed column, consider using open
rowset(bulk)
> instead.
> More details helpful. :)
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/Hello Neo,
N> I realized, I am having SQL Server 2000 for this problem, and not
N> 2005 (To many projects I guess). So, SSIS won't be possible. Same
N> goes for XML typed column.
BCP or SQLXMLBulkLoad then. Not an easy problem to solve with those tools.
If you can use the .NET 2.0 framework, you might want to look into the SqlBu
lkCopy
class. That might help.
Thanks!
Kent

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/