SQL Bulk Load Import - with attachment 
Author Message
 SQL Bulk Load Import - with attachment

I have a bid xml file to import into SQL.
Here'S a little sample for the xml file :

<RX_SPECTACLE Id="SP173F"  EngineId="SP173F"  
Creation_date="2002-01-23T14:05:37" >
  <HEADER>
    <CUSTOMER LoginID="opticraf"  Email="" >
      <ACCOUNTS>
        <ACCOUNT Type="RX1" >
          <ADDRESS Street_Number="17311" />
        </ACCOUNT>
        <ACCOUNT Type="RX2" >
          <ADDRESS Street_Number="17312" />
        </ACCOUNT>
      </ACCOUNTS>
    </CUSTOMER>
  </HEADER>
etc...

Im using the SQL Bulk Load feature to bulk it directly
into SQL tables.
Here's my problem, I would like to put everything in the
same table, but I can't get it to work for sub-element.
I have to make a table for Account and a table for
Address!!!!
I would like to make a table with :
        - AccountType
        - AccountAddress

Here's what I have as a schema :

<xsd:schema xmlns:xsd=" http://www.***.com/ ;
            xmlns:sql="urn:schemas-microsoft-com:mapping-
schema">
<xsd:annotation>
  <xsd:appinfo>
    <sql:relationship name="Rx_Spectacle_Account_Ref"
          parent="Rx_Spectacle"
          parent-key="Key"
          child="Account"
          child-key="Key" />
  </xsd:appinfo>
</xsd:annotation>
  <xsd:element name="RX_SPECTACLE"
sql:relation="Rx_Spectacle"
               sql:key-fields="Key" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="HEADER" sql:is-constant="1" >
          <xsd:complexType>
             <xsd:sequence>
                <xsd:element name="CUSTOMER" sql:is-
constant="1" >
                  <xsd:complexType>
                    <xsd:sequence>
                      <xsd:element name="ACCOUNTS" sql:is-
constant="1" >
                        <xsd:complexType>
                          <xsd:sequence>
                            <xsd:element name="ACCOUNT"
sql:relation="Account"

sql:relationship="Rx_Spectacle_Account_Ref">
                              <xsd:complexType>
                                <xsd:attribute name="Type"
sql:field="AccountType" type="xsd:string" />
                              </xsd:complexType>
                            </xsd:element>
                          </xsd:sequence>
                        </xsd:complexType>
                      </xsd:element>
                    </xsd:sequence>
                  </xsd:complexType>
                </xsd:element>
             </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
     </xsd:sequence>
        <xsd:attribute name="Id" sql:field="Key"  
type="xsd:string" />
        <xsd:attribute name="Creation_date"
sql:field="CreationDate"  type="xsd:string" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

How can I add the address sub-element into the same table
(Account)?

Is it possible to do it???
Please help me, or just tell me if it's possible...

  sqlxml ng.txt
2K Download


Wed, 04 Aug 2004 04:13:20 GMT
 SQL Bulk Load Import - with attachment

Hi ,

Based on my understanding, you want  to create a table which can import all
the nodes from xml.

Here I?d like to give you a method which I have used with your example. I
hope it is helpful to you.




<HEADER>
   <CUSTOMER LoginID="opticraf"? Email="" >
     <ACCOUNTS>
        <ACCOUNT Type="RX1" >
           <ADDRESS Street_Number="17311" />
        </ACCOUNT>
        <ACCOUNT Type="RX2" >
           <ADDRESS Street_Number="17312" />
        </ACCOUNT>
     </ACCOUNTS>
    </CUSTOMER>
</HEADER>'




    Email varchar(20) '../../../Email',
     AccountType? varchar(10) '.',


This posting is provided 'AS IS' with no warranties, and confers no rights.

Regards,
Haiwei Xu
Microsoft Support Engineer



Sun, 08 Aug 2004 13:36:58 GMT
 SQL Bulk Load Import - with attachment

Your example seem great, but the problem is I have a XML file, and it is
not directly into SQL already, and I've already look the into the
FromXML feature, and my file can be up to 12k of size, and the FromXML
feature seems to block after 8k.  So, What I have found is a utility
that can do like a SQL standard bcp, which is called SQLXML, but a Bcp
of an XML file.

With a schema file, that you can see in my first mail, it is possible to
bulk insert into SQL table the information, but I have to make a
different table for each tag that I have, I cant seem to find a way to
bulk load all the info, in separate field, in the same table.  With the
FromXML feature, it could work, but like I say, my objective is to
insert the data into SQL, then for the FromXML feature I have to insert
the data into one field of a sql table before using the FromXML feature.
It could be useful, but since I have the length problem, it is not a
possible way.

The SQLCML way is working fine, Im just searching for a way to bulk load
all the info into the same table, instead of separate table.  DO you
know how to do it?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!



Sun, 08 Aug 2004 22:05:27 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Importing attribute data into SQL Server 2000 with Bulk Load

2. How to import mail attachment to SQL database automatically

3. SQL XML Bulk Load

4. XML View Mapper & SQL Bulk Load

5. Newbie: Creating XDS for SQL XML bulk load

6. SQL XML Bulk Load

7. XML for SQL bug - Bulk Load

8. XML Bulk Load of a header element into two SQL Server 2000 tables

9. SQL XML Bulk Load Requires Database Ownership!

10. SQL Server 2K XML Bulk Load How-To?

11. XML Bulk Load- sql:relationship

12. Bulk Loading XML into SQL


 
Powered by phpBB® Forum Software