December 23, 2013 at 7:06 am
Hi
Please could someone help with the XSD design to process this simple XML content sample (Picture 1) into the SQL table object definition below (Picture 2) using SQLXMLBULKLOAD 4.0?
--Picture 1
<ROOT>
<PACKS>
<PACK>
<PACKID>1328711000001106</PACKID>
<CODEDATA>
<CODE>5017007024019</CODE>
<STARTDT>2009-11-09</STARTDT>
<ENDDT>2013-11-04</ENDDT>
</CODEDATA>
<CODEDATA>
<CODE>5017007019220</CODE>
<STARTDT>2013-11-05</STARTDT>
</CODEDATA>
</PACK>
<PACK>
<PACKID>1328111000001105</PACKID>
<CODEDATA>
<CODE>5012617009784</CODE>
<STARTDT>2010-02-04</STARTDT>
</CODEDATA>
</PACK>
</PACKS>
</ROOT>
--Picture 2
CREATE TABLE [dbo].[PackCode_Details]
(
[PackCode_DetailsId] bigint identity(1,1) NOT NULL,
[PackId] bigint NOT NULL,
[CodeId] varchar(14) NOT NULL,
[StartDt] smalldatetime NOT NULL,
[EndDt] smalldatetime NULL
) ON [PRIMARY]
GO
Any help but would be much appreciated as I've been struggling to work this one out - initally I was thinking of processing into two related tables with PackId and CodeId in one and then CodeId with StartDt and EndDt in the other, however, on bulkimport the data was correct in the second table but the first table missed out some records when there is a history of CodeData (one to many relationship of PACK element to CODEDATA element) whereby it only lists the first CodeId value against the PackId.
Thanks in advance.
Mark
January 5, 2014 at 2:42 pm
Hi, can anyone advise?
January 7, 2014 at 2:16 am
You should be able to load the XML into two tables - as per your original attempt. See attached files.
I've included an XML BulkLoad VB script - if you use this you will need to edit the connection string and file locations.
Hope this helps...
January 8, 2014 at 4:03 am
Thank you Grasshopper - this certainly helps.
I can get the data into two tables now.
My existing process also does a parse of XSD against the XML file before bulkimport of the xml content, this is failing with an error (see attached):-
Not sure why it errors, yet processes the xml content as expected.
Do you have any thoughts?
January 8, 2014 at 5:12 am
The error suggests that the XSD does not contain a 'PACKID' element within the PACK element... The example that worked for me looked like this (for the PACK element):-
<xsd:element name="PACK" sql:relation="PackCode_Details">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="CODEDATA" />
<xsd:element name="PACKID" sql:field="PackId" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
Is this what you have tried?
January 8, 2014 at 7:24 am
Hi
Yes it is the same.
Attached is the parsing code that causes the error.
January 16, 2014 at 8:21 am
Hi,
Sorry for the delay in getting back to you - work getting in the way!
It seems that 'validation' is a lot more strict than loading data using XMLBulLoad. I finally managed to get the XML file validated using the attached XSD. What was missing was explicit maxOccurs="unbounded" for "repeating" elements and minOccurs="0" for optional elements.
Hope this works for you...
January 17, 2014 at 4:07 am
Yes that works a treat:-)
I made the extra validation changes and noticed that the move of the name attribute also needed to be before the reference attribute as you've done in the example
So this,
<xsd:element name="PACKID" sql:field="PackId"/>
<xsd:element ref="CODEDATA" maxOccurs="unbounded"/>
instead of this,
<xsd:element ref="CODEDATA"/>
<xsd:element name="PACKID" sql:field="PackId"/>
Again thanks for your help on this, its much appreciated.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply