Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert JanI don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to
for
that
>
>
Showing posts with label xsd. Show all posts
Showing posts with label xsd. Show all posts
Friday, March 23, 2012
problem queryplan xml template query
Hi,
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql
I use a xsd schema to load XML with a complex structure from a database for
using it in an ASP webpage. Somehow the database uses quite a long time to
make a query plan for the template. The second time the template is used the
database responses quickly, also for other data (using other selection
criteria).
The queryplan is lost when there are no calls for some time, or when a small
change is made to de database stucture, so the next time the ASP page is
called users receive a timeout error.
I have checked all the relevant indexes from the tables that are used for
creating the XML.
Is there any way to influence the speed/persistance of the queryplan that is
created for a xml template query?
Any help will be appreceated,
Albert Jan
I don't think there is. What is happening is that the first time the query
is compiled and then cached. If you don't run it for a while, the query plan
will be purged from the cache and the query will be recompiled. The only way
to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
stored proc and call the stored proc. You can use the SQL Profiler to see
what the query is that is being generated.
Best regards
Michael
"Albert Jan" <awonnink@.hotmail.com> wrote in message
news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
> Hi,
> I use a xsd schema to load XML with a complex structure from a database
> for
> using it in an ASP webpage. Somehow the database uses quite a long time to
> make a query plan for the template. The second time the template is used
> the
> database responses quickly, also for other data (using other selection
> criteria).
> The queryplan is lost when there are no calls for some time, or when a
> small
> change is made to de database stucture, so the next time the ASP page is
> called users receive a timeout error.
> I have checked all the relevant indexes from the tables that are used for
> creating the XML.
> Is there any way to influence the speed/persistance of the queryplan that
> is
> created for a xml template query?
> Any help will be appreceated,
> Albert Jan
>
|||Hi Michael,
I had hoped I woudn't have to redesign the solution, because I like the
technique using the template query. But maybe I'll just have to.
Thank you for your answer.
Albert Jan
"Michael Rys [MSFT]" <mrys@.online.microsoft.com> wrote in message
news:emXkJcUOFHA.3808@.TK2MSFTNGP14.phx.gbl...
> I don't think there is. What is happening is that the first time the query
> is compiled and then cached. If you don't run it for a while, the query
plan
> will be purged from the cache and the query will be recompiled. The only
way[vbcol=seagreen]
> to "persist" the plan is to write the FOR XML EXPLICIT mode query inside a
> stored proc and call the stored proc. You can use the SQL Profiler to see
> what the query is that is being generated.
> Best regards
> Michael
> "Albert Jan" <awonnink@.hotmail.com> wrote in message
> news:O$EwS3POFHA.4028@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
for[vbcol=seagreen]
that
>
>
sql
Problem propagating identity fields for multiple tables in SQLXML Bulkload
I am having a problem importing XML documents into SQL using SQLXML Bulkload with an annotated XSD schema. I have three, hierarchical tables in a SQL 2000 DB: tblBatches -> tblRecords -> tblDetails. The XML document I want to import does not contain identity fields. So I am using '.KeepIdentity = False' in my ActiveX VB Script, so SQL Server will generate the key fields for me. When I run the script, the message I get is 'No data was provided for column 'record_ID' on table 'tblDetails', and this column cannot contain NULL values.' So, basically, it's not inserting the matching foreign key generated for tblRecords into tblDetails.
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql
I have been playing around with this for several days, and if I remove all reference to the third table in the hierarchy, tblDetails, from my XSD schema, the data imports and SQL successfully inserts the identity fields for me for the first two tables. If it will insert the identity fields for two tables, why is it having so much trouble doing it for three? I thought SQLXML 3.0 SP2 was supposed to fix the identity propagation problem. I am using SQLXML 3.0 SP2, Windows 2000, VS.NET 2002, and SQL Server 2000.
I am pasting my VB code, XSD schema, and XML document below. I will also attach copies of them, if that is easier.
Here is the VB code I am using to run the bulkload:
Dim BulkLoad As SQLXMLBulkLoad3
Try
'Create the new XML Bulk Load object
BulkLoad = New SQLXMLBulkLoad3()
'Set the connection string.
BulkLoad.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;" & _
"Persist Security Info=False;User ID=mmsuser;Initial Catalog=MMS;Data Source=NDEWITT\NetSDK;"
'Log any errors.
BulkLoad.ErrorLogFile = "C:\TestSQLErrorLog.xml"
'Have it automatically add the identity fields.
BulkLoad.KeepIdentity = False
BulkLoad.CheckConstraints = True
BulkLoad.Transaction = False
'And perform the data import.
BulkLoad.Execute("C:\TestMMSSchemaNETxsd.xsd", _
"C:\TestXDRNoIdentity.xml")
'Clean up.
Catch ex As Exception
MessageBox.Show(ex.ToString)
Finally
BulkLoad = Nothing
End Try
Here is my XSD schema:
<?xml version="1.0" ?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:dt="urn:schemas-microsoft-com:datatypes" xmlns:sql="urn:schemas-microsoft-com:mapping-schema" xmlns:msch="urn:schemas-microsoft-com:mapping-schema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
<xs:annotation>
<xs:appinfo>
<sql:relationship name="BatchRec" parent="tblBatches" parent-key="batch_ID" child="tblRecords" child-key="batch_ID" />
<sql:relationship name="RecDetail" parent="tblRecords" parent-key="record_ID" child="tblDetails" child-key="record_ID" />
</xs:appinfo>
</xs:annotation>
<xs:element name="statement_batch" sql:relation="tblBatches" sql:key-fields="batch_ID">
<xs:complexType>
<xs:sequence>
<xs:element name="print_batch_key" type="xs:string" />
<xs:element name="print_batch_type_value" type="xs:string" />
<xs:element name="client_program_key" type="xs:string" />
<xs:element name="print_item_quantity" type="xs:int" />
<xs:element name="start_date" type="xs:date" />
<xs:element name="end_date" type="xs:date" />
<xs:element name="client_key" type="xs:string" />
<xs:element name="client_name" type="xs:string" />
<xs:element name="program_key" type="xs:string" />
<xs:element name="program_name" type="xs:string" />
<xs:element name="return_address_line_1" type="xs:string" />
<xs:element name="return_address_line_2" type="xs:string" />
<xs:element name="return_city" type="xs:string" />
<xs:element name="return_state" type="xs:string" />
<xs:element name="return_zip" type="xs:string" />
<xs:element name="miles_synonym" type="xs:string" />
<xs:element name="statement_synonym" type="xs:string" />
<xs:element name="member_id_synonym" type="xs:string" />
<xs:element name="member_synonym" type="xs:string" />
<xs:element name="redemption_http" type="xs:string" />
<xs:element name="redemption_phone" type="xs:string" />
<xs:element name="print_logo" type="xs:string" />
<xs:element name="run_date" type="xs:date" />
<xs:element name="custom_message" type="xs:string" />
<xs:element name="summary_text" type="xs:string" />
<xs:element name="format_type" type="xs:string" />
<xs:element name="envelope_type" type="xs:string" />
<xs:element name="paper_type" type="xs:string" />
<xs:element name="statement_record" sql:relation="tblRecords" sql:relationship="BatchRec" sql:key-fields="record_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="member_id" type="xs:string" />
<xs:element name="first_name" type="xs:string" />
<xs:element name="last_name" type="xs:string" />
<xs:element name="address_line_1" type="xs:string" />
<xs:element name="address_line_2" type="xs:string" />
<xs:element name="city" type="xs:string" />
<xs:element name="state" type="xs:string" />
<xs:element name="zip" type="xs:string" />
<xs:element name="country_id" type="xs:string" />
<xs:element name="employer" type="xs:string" />
<xs:element name="begin_balance" type="xs:int" />
<xs:element name="award_activity" type="xs:int" />
<xs:element name="redeem_activity" type="xs:int" />
<xs:element name="expired_miles" type="xs:int" />
<xs:element name="adjusted_miles" type="xs:int" />
<xs:element name="end_balance" type="xs:int" />
<xs:element name="unvested_balance" type="xs:int" />
<xs:element name="detail_record" sql:relation="tblDetails" sql:relationship="RecDetail" sql:key-fields="detail_ID" minOccurs="1" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="effective_date" type="xs:date" />
<xs:element name="activity_type" type="xs:unsignedByte" />
<xs:element name="activity_description" type="xs:string" />
<xs:element name="activity_miles" type="xs:int" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="MMSSchema" msdata:IsDataSet="true" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element ref="statement_batch" />
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
Here is a sample XML document I want to import:
<statement_batch>
<print_batch_key>609167</print_batch_key>
<print_batch_type_value>Member Statement</print_batch_type_value>
<client_program_key>857148</client_program_key>
<print_item_quantity>108</print_item_quantity>
<start_date>02-13-2004</start_date>
<end_date>03-04-2004</end_date>
<client_key>317229</client_key>
<client_name>CARLSON SYSTEMS</client_name>
<program_key>857143</program_key>
<program_name>Carlson Systems -The Green Mile</program_name>
<return_address_line_1>3475 Piedmont Rd. NE</return_address_line_1>
<return_address_line_2>Suite 300</return_address_line_2>
<return_city>Atlanta</return_city>
<return_state>GA</return_state>
<return_zip>30305</return_zip>
<miles_synonym>Points</miles_synonym>
<statement_synonym>Statement</statement_synonym>
<member_id_synonym>Participant ID</member_id_synonym>
<member_synonym>Participant</member_synonym>
<redemption_http>www.webrewards.com</redemption_http>
<redemption_phone>Merchandise- (877) 690-2080; Travel- (800) 210-8539</redemption_phone>
<print_logo>http://www.webrewards.com/carlson_sy...sonSystems.jpg</print_logo>
<run_date>03-04-2004</run_date>
<custom_message><![CDATA[
<BR>
<BR>]]></custom_message>
<summary_text>
</summary_text>
<format_type>STATEMENT_DETAIL_LOGO</format_type>
<envelope_type>PISTOL_STATEMENT</envelope_type>
<paper_type>STATEMENT_OFFSET_GRAY</paper_type>
<statement_record>
<member_id>859953</member_id>
<first_name>MICHAEL</first_name>
<last_name>STAPLES</last_name>
<address_line_1>4300 DELLWOOD LANE</address_line_1>
<address_line_2>
</address_line_2>
<city>MOUND</city>
<state>MN</state>
<zip>55364</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>313152</begin_balance>
<award_activity>21924</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>335076</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>176</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>265</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1344</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>235</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>3103</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>5435</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>154</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>66</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>500</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>597</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1014</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>756</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>56</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>490</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>212</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1875</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>256</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>240</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>202</activity_miles>
</detail_record>
</statement_record>
<statement_record>
<member_id>859954</member_id>
<first_name>M</first_name>
<last_name>LEGAULT</last_name>
<address_line_1>13156 BITTERSWEET ST NW</address_line_1>
<address_line_2>
</address_line_2>
<city>COON RAPIDS</city>
<state>MN</state>
<zip>55448</zip>
<country_id>USA</country_id>
<employer>
</employer>
<begin_balance>91188</begin_balance>
<award_activity>5569</award_activity>
<redeem_activity>0</redeem_activity>
<expired_miles>0</expired_miles>
<adjusted_miles>0</adjusted_miles>
<end_balance>96757</end_balance>
<unvested_balance>0</unvested_balance>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>80</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>105</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>891</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1543</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
<detail_record>
<effective_date>02-29-2004</effective_date>
<activity_type>1</activity_type>
<activity_description>Award - Points Month February 2004 -- Awarded by: CARLSON SYSTEMS</activity_description>
<activity_miles>1475</activity_miles>
</detail_record>
</statement_record>
</statement_batch>
Any help will be greatly appreciated. Thanks!I discovered your post at Midtown Computer Systems Enterprise web site about SQLXMLBulkLoad and identity columns
in 3 hierarchical tables. I have having similar problems as the ones you described in your post. Were you able to find
a solution? If so, can you share it here? Thanks.sql
Subscribe to:
Posts (Atom)