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
No comments:
Post a Comment