SQLXMLBulkLoad for repeated elements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

SQLXMLBulkLoad for repeated elements

Author

Topic

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-04-21 : 21:43:11

Hi all,

I've been playing around with this API to load in a stack of XML into our database. The data we're getting is structured but I want to denormalise it to suit our schema. The problem is that the source data repeats some elements:


.. other elements
<Address>
    <UnstructuredAddress>
             <AddressLine>2 Balls Court</AddressLine>
             <AddressLine>Tyeham-in-knots</AddressLine>
    </UnstructuredAddress>
</Address>

There could be up to 3 of these AddressLine elements in each record and I want to copy the data to columns AddressLine1, AddressLine2 etc.

Here is the fragment from the XSD used to map to the table:

<xsd:element name="UnstructuredAddress" sql:is-constant="1">
  <xsd:complexType>
    <xsd:sequence>
       <xsd:element name="AddressLine" sql:field="AddressLine1" sql:datatype="varchar" />
       <xsd:element name="AddressLine" sql:field="AddressLine2" sql:datatype="varchar" />
       <xsd:element name="AddressLine" sql:field="AddressLine3" sql:datatype="varchar" />
    </xsd:sequence>
  </xsd:complexType>
</xsd:element>

But it doesn't work. I've tried using XPATH notation like AddressLine[1] but still no joy.

I'm using SQL2K, SQLXML 3.0.

Any ideas?

Cheers,

Tim

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-04-28 : 23:06:41

I ended up working around the issue. Not ideal, but it worked.

I basically omitted these tags from the mapping schema and let them overflow into new column in my staging table. Then I've written a function to parse it into the destination columns.

Cheers,

Tim

UpsideDownTire
Starting Member

6 Posts

Posted - 2009-06-21 : 18:56:36

Hi Tim,

I have a similar such issue except where you had elements all named "AddressLine", I have a "complexType" set of elements.

Here's a demo XML. Notice each "<Participant>" can have a varying number of elements. Each participant is a complexType element in <ParticipantList>...</ParticipantList>. Each ParticipantList is an element within <Conference> as one record.

I have no control over the structure of the XML file.

I'd like to learn how you got your additional fields to "overflow" into new columns. I tried just removing definition of the entire <ParticipantList> from my xsd, but that did not work. I wonder if you can point me the right direction?


<Conference>
        <ConferenceName>Soap Conference</ConferenceName>
        <ScheduledDate>2001-01-01</ScheduledDate>
        <City>San Jose, CA</City>
        <ParticipantList>
                <Participant>
                        <Name>George Smith</Name>
                        <BusinessName>XYZ Soaps</BusinessName>
                        <DisplayTableLocation>C-1</DisplayTableLocation>
                        <DoorPrize>Soap Dispenser</DoorPrize>
                        <RegistrationFeePaid>Yes</RegistrationFeePaid>
                        <SocietyMember>Yes</SocietyMember>
                </Participant>
                <Participant>
                        <Name>Sam Edwards</Name>
                        <BusinessName>Rubber Duck, Inc.</BusinessName>
                        <RegistrationFeePaid>Yes</RegistrationFeePaid>
                        <SocietyMember>No</SocietyMember>
                </Participant>
        </ParticipantList>
</Conference>

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-06-21 : 20:04:06

Here is the xsd element containing the overflow:

<xsd:element name="Row" sql:overflow-field="Overflow">

where "Overflow" is the name of the column in the destination table.

UpsideDownTire
Starting Member

6 Posts

Posted - 2009-06-21 : 20:55:25

Thanks Tim;

Sorry I did not know about that option. And, yes, I should know more. I read up on "sql:overflow-field" and not sure how you would use it in the context of my demo XML. I get the idea it woul d be added to each possible elemnet within the xsd for "Participant", right? Should I therefore define only one xsd for "Participant"?

This is the error I get - though I edited field name using the demo XML I provided - is this text: "In the same scope elements with the same mame, 'BusinessName', have to be the same type."

I think I am getting overwhelmed.

The elements for my "Participant" are numerous. There can be no "Participant" or only two. I have yet to see more than two, but smart to plan for it.

To me "Participant" is like an array. How can one load a varying number of "Participant" into one SQL row?

Thanks,

UDT

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2009-06-21 : 22:08:55

You would need to define your Participants to be written to a separate child table. In the XSD you can define parent-child relationships and the bulk load library can assign foreign keys if you need. I haven't gone that far with mine (I had enough trouble with the overflow stuff), but this link might help:

http://msdn.microsoft.com/en-us/library/aa225754.aspx

UpsideDownTire
Starting Member

6 Posts

Posted - 2009-06-21 : 22:41:04

Wow! OK. I'll consider that idea.

Your original idea brought something to my attemption missed and not previously reported as an error during BulkLoad. So if anything, I gained that knowledge and to you I am very grateful.

So using the new idea, of importing the participants into a separate table, and the xsd to help with that, are you familar with assigning a BIGINT field with an INDENTITY value during BulkLoad?

Tim, thank you very much for your time.

UDT

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources