Bulk Load with Parent / Child failing

I'm trying to bulk load an xml with parent child nodes.
I have setup an sql:relationship and I have also setup the two database
columns as Identity columns. However when I try to load the data, SQL is
telling me that the column can not be NULL ...
Any ideas please ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
Thank you for using Microsoft MSDN newsgroup!
I understand you encountered the issue when importing xml with parent/child
nodes. To get a better idea of the exact issue, will you send the xml files
and script to create the tables in database to me so that I may reproduce
the issue on my side? You could send it to me at petery@.microsoft.com.
Also, you may want to refer to the following article for some know issue:
330582 PRB: "No Data Was Provided for Column" Error Message When You
Propagate Identity Column with SQLXMLBulkLoad
http://support.microsoft.com/defaul...kb;EN-US;330582
I look forward to your reply.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Bulk Load Help - Parent Child relationship in schema

I am running a bulk load from a DTS package. I am able to insert the parent records succesfully into the article table, but I cannot get the child data to insert at all. The package returns an "executed succesfully" message, so I am having a tough time debugging. Here is my schema file, and a sample of the XML file I am trying to load. Please let me know if you spot anything in the schema I can fix (the xml file is supplied to me, and I have no control over it) All I am trying to insert in the child table (xmlTopic) is the Article_ID and the Topic_Type. Thanks in advance.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlARTICLE"
parent-key="ARTICLE_ID"
child="xmlTOPIC"
child-key="ARTICLE_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="ARTICLE" sql:relation="xmlArticle" sql:key-fields="ARTICLE_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="ATTRIBUTION" type="xs:string" sql:field="ATTRIBUTION"/>
<xs:element name="BLURB" type="xs:string" sql:field="BLURB"/>
<xs:element name="BODY" type="xs:string" sql:field="ARTICLE_BODY"/>
<xs:element name="BRAND" type="xs:string" sql:field="BRAND"/>
<xs:element name="BYLINE" type="xs:string" sql:field="BYLINE"/>
<xs:element name="COPYRIGHT" type="xs:string" sql:field="COPYRIGHT"/>
<xs:element name="FEATURE_BLURB" type="xs:string" sql:field="FEATURE_BLURB"/>
<xs:element name="FEATURE_IMAGE" type="xs:string" sql:field="FEATURE_IMAGE"/>
<xs:element name="HEADLINE" type="xs:string" sql:field="HEADLINE"/>
<xs:element name="NEWS_TYPE" type="xs:string" sql:field="NEWS_TYPE"/>
<xs:element name="SOURCE" type="xs:string" sql:field="SOURCE"/>
<xs:element name="TEASER" type="xs:string" sql:field="TEASER"/>
<xs:element name="URL" type="xs:string" sql:field="URL"/>
<xs:element name="TOPICS" sql:mapped="false">
<xs:complexType>
<xs:sequence>
<xs:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic" sql:key-fields="xmlTopicID">
<xs:complexType>
<xs:attribute name="TOPIC_TYPE" type="xs:string" sql:field="TOPIC_TYPE"/>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute name="ARTICLE_ID" type="xs:string" sql:field="ARTICLE_ID"/>
<xs:attribute name="POSTING_DATE" type="xs:string" sql:field="POSTING_DATE"/>
<xs:attribute name="ARCHIVE_DATE" type="xs:string" sql:field="ARCHIVE_DATE"/>
</xs:complexType>
</xs:element>
</xs:schema>

<?xml version="1.0"?>
<!DOCTYPE NEWSFEED SYSTEM "http://www.blah.com">
<NEWSFEED>
<ARTICLE ARTICLE_ID="531320" POSTING_DATE="08-Mar-2006" ARCHIVE_DATE="01-Mar-2007">
<NEWS_TYPE>blah</NEWS_TYPE>
<HEADLINE><![CDATA[blah]]></HEADLINE>
<BLURB><![CDATA[blah]]></BLURB>
<BYLINE><![CDATA[blah]]></BYLINE>
<BODY><![CDATA[blahblahblah]]></BODY>
<ATTRIBUTION><![CDATA[blah]]></ATTRIBUTION>
<SOURCE><![CDATA[blah]]></SOURCE>
<FEATURE_BLURB><![CDATA[blah]]></FEATURE_BLURB>
<FEATURE_IMAGE></FEATURE_IMAGE>
<TEASER><![CDATA[blah]]></TEASER>
<COPYRIGHT><![CDATA[blah]]></COPYRIGHT>
<BRAND><![CDATA[vlah]]></BRAND>
<URL><![CDATA[blah]></URL>
<TOPICS>
<TOPIC TOPIC_TYPE="BLPR"/>
<TOPIC TOPIC_TYPE="BLPR2"/>
</TOPICS>
</ARTICLE>
</NEWSFEED>

Well it turns out I answered my own question. I checked the MSDN Library(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sqlxml3/htm/bulkload_7pv0.asp - see sql:relationship and the Key Ordering Rule), and found the following info:

"This means that in defining the <Customer> element, you must specify the CustomerID attribute in the schema before you specify <sql:relationship>. Otherwise, when an <Order> element enters into scope, XML Bulk Load generates a record for the CustOrder table; and when the XML Bulk Load reaches the </Order> end tag, it sends the record to SQL Server without the CustomerID foreign key column value."

So I put my sql:relationship at the end of the schema file, and it worked like a charm. As reference, here is my revised schema file:

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

<xsd:element name="ARTICLE" sql:relation="xmlArticle">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ATTRIBUTION" type="xsd:string" sql:field="ATTRIBUTION"/>
<xsd:element name="BLURB" type="xsd:string" sql:field="BLURB"/>
<xsd:element name="BODY" type="xsd:string" sql:field="ARTICLE_BODY"/>
<xsd:element name="BRAND" type="xsd:string" sql:field="BRAND"/>
<xsd:element name="BYLINE" type="xsd:string" sql:field="BYLINE"/>
<xsd:element name="COPYRIGHT" type="xsd:string" sql:field="COPYRIGHT"/>
<xsd:element name="FEATURE_BLURB" type="xsd:string" sql:field="FEATURE_BLURB"/>
<xsd:element name="FEATURE_IMAGE" type="xsd:string" sql:field="FEATURE_IMAGE"/>
<xsd:element name="HEADLINE" type="xsd:string" sql:field="HEADLINE"/>
<xsd:element name="NEWS_TYPE" type="xsd:string" sql:field="NEWS_TYPE"/>
<xsd:element name="SOURCE" type="xsd:string" sql:field="SOURCE"/>
<xsd:element name="TEASER" type="xsd:string" sql:field="TEASER"/>
<xsd:element name="URL" type="xsd:string" sql:field="URL"/>
<xsd:element name="TOPIC" sql:relation="xmlTopic" sql:relationship="ArticleTopic">
<xsd:complexType>
<xsd:attribute name="TYPE2" type="xsd:string" sql:field="TopicType"/>
<xsd:attribute name="TOPIC_TYPE" type="xsd:string" sql:field="TopicID"/>
<xsd:attribute name="SIGNIFICANCE_FACTOR" type="xsd:string" sql:field="SignificanceFactor"/>
</xsd:complexType>
</xsd:element>
</xsd:sequence>
<xsd:attribute name="ARTICLE_ID" type="xsd:string" sql:field="ARTICLE_ID"/>
<xsd:attribute name="POSTING_DATE" type="xsd:string" sql:field="POSTING_DATE"/>
<xsd:attribute name="ARCHIVE_DATE" type="xsd:string" sql:field="ARCHIVE_DATE"/>
</xsd:complexType>
</xsd:element>

<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="ArticleTopic"
parent="xmlArticle"
parent-key="ARTICLE_ID"
child="xmlTopic"
child-key="ARTICLE_ID" />
</xsd:appinfo>
</xsd:annotation>

</xsd:schema>

sql

Bulk load and passing parameters in

I'm doing a Bulk Load of an XML file.
The file consists of a parent/child relationship joined by an interger
column in both tables.
The parent table may already have some values in it.
How can I pass in the starting value and also have it auto increment for
each element ?
Michael Tissington
http://www.oaklodge.com
http://www.sqlview.netHello Michael,
If I understand this correctly, you parent table has a column (int)
referenced by a clild table. The parent table have some rows. You want to
change the column to identiy, and pass a starting value to this column. If
I'm off-base, please let me know.
Based on my research, you could not pass starting value or change column
propperty via mapping file. You may want to use "alter table" to change the
column. You could change this in Enterprise Manager, or you need to write
a sql code to do this by creating a temp table, copy from original table
when SET IDENTITY_INSERT is set to on, and then rename the temp table to
the original one. You may want to use profiler to capture the trace when
changing this in Enterprise Manager
You may want to refer to the following article to for some related
information
Using XML Bulk Load with Identity Columns
http://www.sqlmag.com/Article/Artic...rver_40239.html
Hope this is helpful. Please post back if you have further questions.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Bulk insert with child records

Hi,

Maybe this is a really silly problem, but I was wondering if anyone could help me find a solution.

On a daily basis I have to insert about a 1000 new (parent) records in table, with about 25000 corresponding (child) records.

The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.

I would like to use a bulk insert statement to insert the records for both tables, but the problem is that the foreign key in the child table is not automatically updated to the new value of the primary key in the parent table.
Does anyone know a work-around?? Maybe there's a better way of inserting the records?Originally posted by ChrisHens
The two tables a related through a parent-child relation (obviously). The primary keys in both tables are identities. The foreign key in the child table is linked to the primary key in the parent table.


If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.|||Originally posted by DoktorBlue
If I understand your problem right, you want to translate an existing foreign-key-relation into a new one, based on identities?

You need to insert your parent records first, but you will have to keep your old key in your parent table. During insert of your child records, join with the parent table on your old key, and insert the newly created key as foreign key into the child table.

Yes that's exactly what I am trying to achieve. But if I am to keep my old key in the parent table, wouldn't I need an extra field? Wouldn't this also imply that after the insert of the parent and child records that I will have to somehow set the old parent key to a default value? Otherwise future joins might fail right?|||You are correct in all your points:
1) you need the old ID as an extra field, but this isn't really a problem. Often, the old ID is even functional to allow te refer to your source system.
2) if you expect to get the same parent ID in the future again, you can "flush" your old IDs after processing, or you can add some batch processing control. Options are to add a batch number (as an extra field or as unifying part of your old ID), or you can consider to make a batch control table, storing the last new ID (assuming that the identity is continue increasing) of your previous batch.

Options enough, it's depending on your situation; I'm curious what you will do.

Bulk copying xml child node data

I am trying to bulk copy some XML data into a SQL and am generally doing quite well. The XML data I have been given has a child node called "name" which is the same as in the parent node as shown in the highlight of the XML source below. Now I can retrive the data in the parent node using

bulk.ColumnMappings.Add("name", "Name")

but I cannot get any of the data from the child node "Catagory" or " Catagories". Have you any suggestions on how I can get this data.

<?xml version="1.0" ?>

<products>

<product>

<ProductId>12345</ProductId>

<name>Productname</name>

<description>"This is some description text"</description>

<Categories>

<Category>

<name>Category type</name>

<merchantName>Category subtype</merchantName>

</Category>

</Categories>

<fields/>

</product>

Etc…….

</products>

Many thanks in advance

Simon

How did you map to the "Catagory" or " Catagories" node? Make sure you've made proper mapping. For example if you read the xml into a DataSet, then the "Catagory"/" Catagories" will be in 2nd/3rd table respectively, because it is the 2nd/3rd level node (ignore the root node). I mean you can read the xml data use something like this:

Dim ds As New DataSet
ds.ReadXml("c:\XmlFiles\Products.xml", XmlReadMode.Auto)