Importing XML data into a SQL Server database

Hello,
I am having a bit of a problem, and maybe someone out there can
help. The Schema file (test_schema.xml) is:
<?xml version="1.0" ?>
<xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="Root">
<xs:complexType>
<xs:sequence>
<xs:element name="Truserv" sql:mapped="false">
<xs:complexType>
<xs:sequence>
<xs:element name="Item" sql:relation="Test_Table">
<xs:complexType>
<xs:attribute name="item_nbr" type="xs:unsignedInt"
use="required" sql:field="Test_Col1" />
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
The XML file is:
<ROOT>
<TRU_SERV>
<Items test_attribute1="This is a test" />
</TRU_SERV>
</ROOT>
and my vb script code is:
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad.3.0")
objBL.ConnectionString = "provider=SQLOLEDB.1;data
source=aaa;database=xxx;uid=yyy;pwd=zzz"
objBL.ErrorLogFile = "error.log"
objBL.Execute "test_schema.xml", "test_xml.xml"
Set objBL = Nothing
When I run the vb code, the code will execute, but it will not insert
the data into the database. It just runs and from my end, seems to do
nothing. It does connect to the database, since I am not getting any
errors. If anyone out there has any ideas, please let me know.
-Jay
(patel@.cs.utk.edu)
In the XML file one of the tags is called <TRU_SERV>
where as in the schema it is called <xs:element name="Truserv" sql:mapped="false"> These names must match exactly for this to work.
|||also you have the same problem with "item" and "items"

Importing XML Data 2 SQL SERVER

I am importing XML data to SQL SERVER using Bulk load component.
The XML file is like the following:
<?xml version="1.0" encoding="UTF-8" ?>
<legion-event-file>
<header>
<file-id>247</file-id>
<file-generated-date-time>2004-11-21T05:00:09</file-generated-date-time>
</header>
<item>
<item-id>001</item-id>
<legion-operation-id>15091</legion-operation-id>
<legion-customer-id>42828</legion-customer-id>
<legion-user-id>43722</legion-user-id>
<username>yinoue</username>
<performed-by-username>yinoue</performed-by-username>
<performed-by-user-id>43722</performed-by-user-id>
<b-customer-id>1454103</b-customer-id>
<operation-date-time>2004-11-20T10:24:23</operation-date-time>
<preferred-email>yinoue@.bmail.com.au</preferred-email>
<activation>
<indicator>INDICATOR</indicator>
<b-contract-type-code>BNETU</b-contract-type-code>
<provided-activation-code>492189440</provided-activation-code>
</activation>
</item>
</legion-event-file>
And the XSD/XDR is like:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:complexType name="ActivationType">
<xsd:sequence>
<xsd:element name="indicator" type="xsd:string"
sql:field="activation_indicator" />
<xsd:element name="b-contract-type-code" type="xsd:string"
sql:field="b_contract_type_code" />
<xsd:element name="provided-activation-code" type="xsd:string"
sql:field="provided_activation_code" />
</xsd:sequence>
</xsd:complexType>
<xsd:complexType name="DeactivationType">
<xsd:sequence>
<xsd:element name="indicator" type="xsd:string"
sql:field="deactivation_indicator" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="activation" type="ActivationType"
sql:relation="activation" />
<xsd:element name="deactivation" type="DeactivationType"
sql:relation="deactivation"/>
<xsd:complexType name="ItemType">
<xsd:sequence>
<xsd:element name="item-id" type="xsd:string" sql:field="item_id" />
<xsd:element name="legion-operation-id" type="xsd:string"
sql:field="legion_operation_id" />
<xsd:element name="legion-customer-id" type="xsd:string"
sql:field="legion_customer_id" />
<xsd:element name="legion-user-id" type="xsd:string"
sql:field="legion_user_id" />
<xsd:element name="username" type="xsd:string" sql:field="username" />
<xsd:element name="performed-by-username" type="xsd:string"
sql:field="performed_by_username" />
<xsd:element name="performed-by-user-id" type="xsd:string"
sql:field="performed_by_user_id" />
<xsd:element name="b-customer-id" type="xsd:string"
sql:field="b_customer_id" />
<xsd:element name="operation-date-time" type="xsd:string"
sql:field="operation_date_time" />
<xsd:element name="preferred-email" type="xsd:string"
sql:field="preferred_email" />
<xsd:element ref="activation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="item" parent-key="item_id"
child="activation" child-key="item_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
<xsd:element ref="deactivation">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="item" parent-key="item_id"
child="deactivation" child-key="item_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
<xsd:element name="item" type="ItemType" sql:key-fields="item_id"
sql:relation="item" />
<xsd:complexType name="HeaderType">
<xsd:sequence>
<xsd:element name="file-id" type="xsd:string" sql:field="legion_file_id" />
<xsd:element name="file-generated-date-time" type="xsd:string"
sql:field="file_generated_date_time" sql:datatype="varchar" />
</xsd:sequence>
</xsd:complexType>
<xsd:element name="header" type="HeaderType"
sql:key-fields="legion_file_id" sql:relation="legion_file"/>
<xsd:element name="legion-event-file" sql:is-constant="1">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="header"/>
<xsd:element ref="item">
<xsd:annotation>
<xsd:appinfo>
<sql:relationship parent="legion_file" parent-key="legion_file_id"
child="item" child-key="legion_file_id" inverse="true" />
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
It gets error like:
[CDATA[No data was provided for column 'legion_file_id' on table 'item', and
this column cannot contain NULL values.]]
Can anyone tell me whether I should get rid of the non-relation element like
'header'?
Thanks for any feedback
Your problem seems to be some data is missing that is required. Why would
you get rid of "header"? That might solve the problem by eliminating the
field that is missing a value.
Irwin Dolobowsky
Program Manager, SqlXml
http://msdn.microsoft.com/xml
This posting is provided "AS IS" with no warranties, and confers no rights.
"Anson Luo" <Anson Luo@.discussions.microsoft.com> wrote in message
news:E95B527F-4D1F-4FFC-A488-D84FEA1369C4@.microsoft.com...
>I am importing XML data to SQL SERVER using Bulk load component.
> The XML file is like the following:
> <?xml version="1.0" encoding="UTF-8" ?>
> <legion-event-file>
> <header>
> <file-id>247</file-id>
> <file-generated-date-time>2004-11-21T05:00:09</file-generated-date-time>
> </header>
> <item>
> <item-id>001</item-id>
> <legion-operation-id>15091</legion-operation-id>
> <legion-customer-id>42828</legion-customer-id>
> <legion-user-id>43722</legion-user-id>
> <username>yinoue</username>
> <performed-by-username>yinoue</performed-by-username>
> <performed-by-user-id>43722</performed-by-user-id>
> <b-customer-id>1454103</b-customer-id>
> <operation-date-time>2004-11-20T10:24:23</operation-date-time>
> <preferred-email>yinoue@.bmail.com.au</preferred-email>
> <activation>
> <indicator>INDICATOR</indicator>
> <b-contract-type-code>BNETU</b-contract-type-code>
> <provided-activation-code>492189440</provided-activation-code>
> </activation>
> </item>
> </legion-event-file>
> And the XSD/XDR is like:
> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
> xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
> <xsd:complexType name="ActivationType">
> <xsd:sequence>
> <xsd:element name="indicator" type="xsd:string"
> sql:field="activation_indicator" />
> <xsd:element name="b-contract-type-code" type="xsd:string"
> sql:field="b_contract_type_code" />
> <xsd:element name="provided-activation-code" type="xsd:string"
> sql:field="provided_activation_code" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:complexType name="DeactivationType">
> <xsd:sequence>
> <xsd:element name="indicator" type="xsd:string"
> sql:field="deactivation_indicator" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="activation" type="ActivationType"
> sql:relation="activation" />
> <xsd:element name="deactivation" type="DeactivationType"
> sql:relation="deactivation"/>
> <xsd:complexType name="ItemType">
> <xsd:sequence>
> <xsd:element name="item-id" type="xsd:string" sql:field="item_id" />
> <xsd:element name="legion-operation-id" type="xsd:string"
> sql:field="legion_operation_id" />
> <xsd:element name="legion-customer-id" type="xsd:string"
> sql:field="legion_customer_id" />
> <xsd:element name="legion-user-id" type="xsd:string"
> sql:field="legion_user_id" />
> <xsd:element name="username" type="xsd:string" sql:field="username" />
> <xsd:element name="performed-by-username" type="xsd:string"
> sql:field="performed_by_username" />
> <xsd:element name="performed-by-user-id" type="xsd:string"
> sql:field="performed_by_user_id" />
> <xsd:element name="b-customer-id" type="xsd:string"
> sql:field="b_customer_id" />
> <xsd:element name="operation-date-time" type="xsd:string"
> sql:field="operation_date_time" />
> <xsd:element name="preferred-email" type="xsd:string"
> sql:field="preferred_email" />
> <xsd:element ref="activation">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="item" parent-key="item_id"
> child="activation" child-key="item_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> <xsd:element ref="deactivation">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="item" parent-key="item_id"
> child="deactivation" child-key="item_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="item" type="ItemType" sql:key-fields="item_id"
> sql:relation="item" />
> <xsd:complexType name="HeaderType">
> <xsd:sequence>
> <xsd:element name="file-id" type="xsd:string" sql:field="legion_file_id"
> />
> <xsd:element name="file-generated-date-time" type="xsd:string"
> sql:field="file_generated_date_time" sql:datatype="varchar" />
> </xsd:sequence>
> </xsd:complexType>
> <xsd:element name="header" type="HeaderType"
> sql:key-fields="legion_file_id" sql:relation="legion_file"/>
> <xsd:element name="legion-event-file" sql:is-constant="1">
> <xsd:complexType>
> <xsd:sequence>
> <xsd:element ref="header"/>
> <xsd:element ref="item">
> <xsd:annotation>
> <xsd:appinfo>
> <sql:relationship parent="legion_file" parent-key="legion_file_id"
> child="item" child-key="legion_file_id" inverse="true" />
> </xsd:appinfo>
> </xsd:annotation>
> </xsd:element>
> </xsd:sequence>
> </xsd:complexType>
> </xsd:element>
> </xsd:schema>
> It gets error like:
> [CDATA[No data was provided for column 'legion_file_id' on table 'item',
> and
> this column cannot contain NULL values.]]
> Can anyone tell me whether I should get rid of the non-relation element
> like
> 'header'?
> Thanks for any feedback
>
sql

Importing stored procedures from 1 db to another?

Hi there,

Can anyone help?

I just recently imported my Sql server express 2005 db into the full version of sql server 2005... All went according to plan (i used import in studio management 2005 vers not 2005 express) and the data was imported but no stored procedures went with it...

it seemed to only include tables and views...

Is there another way round this?

Unfortunatley i don't have FULL access to the SQL Server 2005 as this is at my hosts... but i have full access to the sql server 2005 express which is locally on my machine

Any advice would be really appreciated

Thanks

ian

Several of the easiest ways:

1. backup the database and restore it to your new server

2. In Object Explorer, you can script out the stored procedures and apply them to the new server

3. Use snapshot replication, but I'd stick with #1 or #2 first.

|||

The previous post is correct - the easiest way to make sure you get all objects from one DB to another is to back it up and restore it to another.

If you don't own the other box that might not be possible. You've already got the data, so to get the other objects you can right-click each and script them out. You can also use the scripting model in DMO to do the same thing, but get all of the objects at once. I have a series of tutorials on that starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=110

Buck Woody

Carpe Datum

Importing data into SQL Server 2005 Eval version

Just installed SQL Server 2005 Eval version and Management Studio does not display any Import/Export functions to load data into tables of an existing database. I thought that this feature was turned off only in Management Studio Express.

Using the Object Explorer, right-click on the database, select [Tasks], then [Import Data...] or [Export Data...]