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.
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?
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)
|||You could look up the Schema. Run this in Query Analyzer and adjust accordingly:
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
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?
>
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.
>
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.
>
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 w
ly 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:
>
.
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 w
ly feed from this vendor and willneed 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 w
ly 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
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
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
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/
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
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.
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.
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
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/
I need to Import an XML file into SQL Database using SSIS, Plz let me know
how do I acheive this. I am quite new to SSIS, So if you can Point me to som
e
samples on importing XML files using SSIS, nothing like that. Any additional
references, if any, are most welcome.
Thanks in Advance,
Sachin R. Chavan.Hello Sachin,
> I need to Import an XML file into SQL Database using SSIS, Plz let me
> know how do I acheive this. I am quite new to SSIS, So if you can
> Point me to some samples on importing XML files using SSIS, nothing
> like that. Any additional references, if any, are most welcome.
a. Get this book and read it: http://www.wrox.com/WileyCDA/WroxTi...>
4584359.html
b. Use the XML source.
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/
Importing XML File via URL using MSSQL 2005
I have an XML file that is hosted on a mainframe web server. I need to read
the file via a URL and import this into my SQL database.
I'm fairly new to SQL 2005 but this seems to be a simple task. I've looked
at using the Import Export wizard but I don't see a simpl way to do this.
I've tried selecting "Flat File" as my data source but this does not seem to
be able to read XML. I've tried the SQLSML data sources but I don't think
this will work if the web server is not IIS.
Does anyone have any ideas?"Bryan" <Bryan@.discussions.microsoft.com> wrote in message
news:37B66D17-FE91-4CFD-B502-DB80BD2B8360@.microsoft.com...
>I have an XML file that is hosted on a mainframe web server. I need to
>read
> the file via a URL and import this into my SQL database.
> I'm fairly new to SQL 2005 but this seems to be a simple task. I've
> looked
> at using the Import Export wizard but I don't see a simpl way to do this.
> I've tried selecting "Flat File" as my data source but this does not seem
> to
> be able to read XML. I've tried the SQLSML data sources but I don't think
> this will work if the web server is not IIS.
> Does anyone have any ideas?
Write a SQLCLR function in C# or VB to retrieve the data from the web
server.
the file via a URL and import this into my SQL database.
I'm fairly new to SQL 2005 but this seems to be a simple task. I've looked
at using the Import Export wizard but I don't see a simpl way to do this.
I've tried selecting "Flat File" as my data source but this does not seem to
be able to read XML. I've tried the SQLSML data sources but I don't think
this will work if the web server is not IIS.
Does anyone have any ideas?"Bryan" <Bryan@.discussions.microsoft.com> wrote in message
news:37B66D17-FE91-4CFD-B502-DB80BD2B8360@.microsoft.com...
>I have an XML file that is hosted on a mainframe web server. I need to
>read
> the file via a URL and import this into my SQL database.
> I'm fairly new to SQL 2005 but this seems to be a simple task. I've
> looked
> at using the Import Export wizard but I don't see a simpl way to do this.
> I've tried selecting "Flat File" as my data source but this does not seem
> to
> be able to read XML. I've tried the SQLSML data sources but I don't think
> this will work if the web server is not IIS.
> Does anyone have any ideas?
Write a SQLCLR function in C# or VB to retrieve the data from the web
server.
Importing XML File using SSIS (DTS)
I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.
Has anyone tried this? If so and you were successful, what were the steps to set up the package?
Thanks.
Have you tried to use XML Source adapter?
- Grab a Data Flow task from the toolbox
- Double-click on the added task
- Double-click on the XML Source in the data flow toolbox
- Double-click added component on the diagram
- Set-up your XML sorce
HTH.
|||Hi HTH,
After reading your post, i was trying to dothe same thing using the below xml:
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
and i even generated the XSD using SSIS and got this :
<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="country">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="population" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
and now after pointing things correctly in my connection manager when i go to my columns all i get is one column which says Name..
why is it so? ideally i should get Name and Pollution right..? Can you advice..?
|||
Try wrapping the xml in a root node:
<root>
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
</root>
Thanks
Mark
Mark,
Thanks 
It worked.
So the impression that i get is that all XML's should be waped in a root node. Is it True
Importing XML File using SSIS (DTS)
I can use MS Access to import an XML file and it builds the columns and loads the data. I am trying to use SQL Server 2005 to do the same thing and there doesn't seen to be a way to easily accomplish the same task that works so well in Access.
Has anyone tried this? If so and you were successful, what were the steps to set up the package?
Thanks.
Have you tried to use XML Source adapter?
- Grab a Data Flow task from the toolbox
- Double-click on the added task
- Double-click on the XML Source in the data flow toolbox
- Double-click added component on the diagram
- Set-up your XML sorce
HTH.
|||Hi HTH,
After reading your post, i was trying to dothe same thing using the below xml:
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
and i even generated the XSD using SSIS and got this :
<?xml version="1.0"?>
<xs:schema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="country">
<xs:complexType>
<xs:sequence>
<xs:element minOccurs="0" name="name" type="xs:string" />
<xs:element minOccurs="0" name="population" type="xs:decimal" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
and now after pointing things correctly in my connection manager when i go to my columns all i get is one column which says Name..
why is it so? ideally i should get Name and Pollution right..? Can you advice..?
|||
Try wrapping the xml in a root node:
<root>
<country
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="country.xsd">
<name>France</name>
<population>59.7</population>
</country>
</root>
Thanks
Mark
Mark,
Thanks It worked.
So the impression that i get is that all XML's should be waped in a root node. Is it True
importing xml file to sql server
Hello there
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?Roy Goldhammer wrote:
> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?Roy Goldhammer wrote:
> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/sql
importing xml file to sql server
Hello there
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?
Roy Goldhammer wrote:
> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
I have an xml file that i need to compare it to data on server. and i don't
have xlt file (which is the base of xml as far as i know)
I need to import the xml file with the entire structure to sql server in
order to compare it with data i have
how can i do that?
Roy Goldhammer wrote:
> I need to import the xml file with the entire structure to sql server in
> order to compare it with data i have
> how can i do that?
Have a look at the OPENXML clause
<http://msdn2.microsoft.com/en-us/library/ms191268.aspx>
Martin Honnen -- MVP XML
http://JavaScript.FAQTs.com/
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...
>
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...
>
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.
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
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
--
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
--
Subscribe to:
Posts (Atom)