importing xml document to multiple related table with identity column.

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

importing XML Data into SQL Server 2000

Hi All,
I need to import data from a large XML file.
I do not want to use XML Bulk upload or use ADO components.
I am looking for a way to use sql server scripts to open
this file through a ODBC driver , OpenRowset function.
Are there any ODBC or OLEDB drivers exist that can be used
in OpenRowset function ?
Not that I know of.
You may want to look at using a stored proc and OpenXML (although XML
Bulkload is more efficient).
Sorry.
Michael
"vaga" <anonymous@.discussions.microsoft.com> wrote in message
news:00a101c49b3b$9b4c2150$a301280a@.phx.gbl...
> Hi All,
> I need to import data from a large XML file.
> I do not want to use XML Bulk upload or use ADO components.
> I am looking for a way to use sql server scripts to open
> this file through a ODBC driver , OpenRowset function.
> Are there any ODBC or OLEDB drivers exist that can be used
> in OpenRowset function ?
>

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 XML data

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

Importing XML data

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

Importing XML data

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