Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 SqlXmlBulkLoad загадки применения sql:key-fields  [new]
alek2
Guest
Схема (рабочая)
+

<!--
http://www.utilities-online.info
-->

<xsd:schema 
            xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema"

            xmlns:sn="http://project.selena-online.ru" 
            targetNamespace="http://project.selena-online.ru"
            elementFormDefault="qualified"
>

  <xsd:annotation>
    <xsd:appinfo>

      <sql:relationship name="ApartmentsLocation"
          parent="xml_apartments"
          parent-key="partnerId objectid unitid id"
          child="xml_apartmentslocation"
          child-key="partnerId objectid unitid id" 
      />


      <sql:relationship name="ApartmentsFlags"
          parent="xml_apartments"
          parent-key="partnerId objectid unitid id"
          child="xml_apartmentsflags"
          child-key="partnerId objectid unitid id" 
      />

      <sql:relationship name="ApartmentsAges"
          parent="xml_apartments"
          parent-key="partnerId objectid unitid id"
          child="xml_apartmentsages"
          child-key="partnerId objectid unitid id" 
      />

    </xsd:appinfo>
  </xsd:annotation>


  <xsd:element name="selena" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>

        <xsd:element name="apartments" sql:is-constant="1" minOccurs="0" maxOccurs="1">
          <xsd:complexType>
           <xsd:sequence>

            <xsd:element name="apartment" minOccurs="0" maxOccurs="unbounded"
                           sql:relation="xml_apartments" 
                           sql:key-fields="partnerId objectid unitid id"
                           sql:overflow-field="OverflowColumn"
            >
             <xsd:complexType>
               <xsd:sequence>

                 <xsd:element name="description" type="xsd:string" maxOccurs="1" />
                 <xsd:element name="details" type="xsd:string" maxOccurs="1" />
                 <xsd:element name="rooms" type="xsd:string" maxOccurs="1" />

                 <xsd:element name="location"  maxOccurs="1"
                           sql:relation="xml_apartmentslocation" 
                           sql:key-fields="partnerId objectid unitid id"
                           sql:relationship="ApartmentsLocation" 

                 >
                  <xsd:complexType>
                    <xsd:attribute name="places" type="xsd:int" use="required" /> 
                    <xsd:attribute name="addplaces" type="xsd:int" use="required" /> 
                    <xsd:attribute name="childplaces" type="xsd:int" use="required" /> 

                    <xsd:attribute name="id"        type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"    type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid"  type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                 </xsd:complexType>
                 </xsd:element>

                 <xsd:element name="wholly" maxOccurs="1"
                           sql:relation="xml_apartmentsflags" 
                           sql:key-fields="partnerId objectid unitid id flag"
                           sql:relationship="ApartmentsFlags" 

                 >
                  <xsd:complexType>
                     <xsd:attribute name="flag" type="xsd:string" default="wholly" /> 
                     <xsd:attribute name="value" type="xsd:boolean" use="required" /> 

                    <xsd:attribute name="id"       type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                  </xsd:complexType>
                 </xsd:element>

                 <xsd:element name="regular" maxOccurs="1"

                           sql:relation="xml_apartmentsflags" 
                           sql:key-fields="partnerId objectid unitid id flag"
                           sql:relationship="ApartmentsFlags" 
                 >
                  <xsd:complexType>
                    <xsd:attribute name="flag" type="xsd:string" default="regular" /> 
                    <xsd:attribute name="value" type="xsd:boolean" use="required" /> 

                    <xsd:attribute name="id"       type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                  </xsd:complexType>
                 </xsd:element>


                 <xsd:element name="lock_possible" maxOccurs="1"
                           sql:relation="xml_apartmentsflags" 
                           sql:key-fields="partnerId objectid unitid id flag"
                           sql:relationship="ApartmentsFlags" 
                 >
                  <xsd:complexType>
                    <xsd:attribute name="flag" type="xsd:string" default="lock_possible" /> 
                    <xsd:attribute name="value" type="xsd:boolean" use="required" /> 

                    <xsd:attribute name="id"       type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                  </xsd:complexType>
                 </xsd:element>

                 <xsd:element name="dayweek_price" maxOccurs="1"
                           sql:relation="xml_apartmentsflags" 
                           sql:key-fields="partnerId objectid unitid id flag"
                           sql:relationship="ApartmentsFlags" 
                 >
                  <xsd:complexType>
                    <xsd:attribute name="flag" type="xsd:string" default="dayweek_price" /> 
                    <xsd:attribute name="value" type="xsd:boolean" use="required" /> 

                    <xsd:attribute name="id"       type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                  </xsd:complexType>
                 </xsd:element>


                 <xsd:element name="ages"  maxOccurs="1"
                           sql:relation="xml_apartmentsages" 
                           sql:key-fields="partnerId objectid unitid id"
                           sql:relationship="ApartmentsAges" 
                 >
                  <xsd:complexType>
                    <xsd:attribute name="main" type="xsd:string" use="required" /> 
                    <xsd:attribute name="add" type="xsd:string" use="required" sql:field="[add]" /> 
                    <xsd:attribute name="child" type="xsd:string" use="required" /> 

                    <xsd:attribute name="id"       type="xsd:int" use="required" /> 
                    <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
                    <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
                    <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 
                  </xsd:complexType>
                 </xsd:element>

               </xsd:sequence>

               <xsd:attribute name="id"       type="xsd:int" use="required" /> 
               <xsd:attribute name="unitid"   type="xsd:int" use="required" /> 
               <xsd:attribute name="objectid" type="xsd:int" use="required" /> 
               <xsd:attribute name="partnerId" type="xsd:int" default="-1" /> 

             </xsd:complexType>
            </xsd:element>
      
           </xsd:sequence>
          </xsd:complexType>
        </xsd:element>

     </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

</xsd:schema>



Данные для примеру
apartmentlist_4.xml
+
<?xml version="1.0" encoding="utf-8"?>
<selena xmlns="http://project.selena-online.ru" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://project.selena-online.ru https://selena-online.ru/static/rest/apartmentlist.xsd">
<apartments>
    <apartment id="26" unitid="11" objectid="4">
        <description>КПК А (повышенной комфортности)</description>
        <details></details>
        <rooms>1,2,3,4,5,6,8</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="true"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="25" unitid="11" objectid="4">
        <description>Люкс</description>
        <details></details>
        <rooms>А,Б</rooms>
        <location places="2" addplaces="1" childplaces="1" />
        <wholly value="true"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00011" add="01110" child="01100"/>
    </apartment>
    <apartment id="31" unitid="12" objectid="4">
        <description>1-местные 1 класса с доп.местом</description>
        <details></details>
        <rooms>47,49,50,51,52,54</rooms>
        <location places="1" addplaces="1" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00001" add="01111" child="01100"/>
    </apartment>
    <apartment id="30" unitid="12" objectid="4">
        <description>2-местные 1А класса</description>
        <details></details>
        <rooms>55,58,59,60,61,62,63</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="32" unitid="12" objectid="4">
        <description>2-местные 2 класса 2-ярусные</description>
        <details></details>
        <rooms>29,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,48</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="34" unitid="13" objectid="4">
        <description>2-местная 1А класса</description>
        <details></details>
        <rooms>84С</rooms>
        <location places="2" addplaces="0" childplaces="0" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="00000"/>
    </apartment>
    <apartment id="41" unitid="12" objectid="4">
        <description>КПК С (каюты повышенной комфортности - кормовая часть теплохода)</description>
        <details></details>
        <rooms>64,65,66,67,68,69</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="true"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="01111" add="00000" child="01100"/>
    </apartment>
    <apartment id="36" unitid="13" objectid="4">
        <description>2-местная 2А класса с доп.местом</description>
        <details></details>
        <rooms>98</rooms>
        <location places="2" addplaces="1" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="11111" child="01100"/>
    </apartment>
    <apartment id="28" unitid="12" objectid="4">
        <description>Полулюкс</description>
        <details></details>
        <rooms>22,23</rooms>
        <location places="2" addplaces="1" childplaces="1" />
        <wholly value="true"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="11110" child="01100"/>
    </apartment>
    <apartment id="29" unitid="12" objectid="4">
        <description>КПК В  (повышенной комфортности - носовая часть теплохода)</description>
        <details></details>
        <rooms>24,25,26,27,28,30</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="true"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="35" unitid="13" objectid="4">
        <description>2-местные 2А класса 2-ярусные</description>
        <details></details>
        <rooms>88,89,90,91,92,93,94,95,96,97,99,101,102,103</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="39" unitid="14" objectid="4">
        <description>2-местные 3 класса</description>
        <details></details>
        <rooms>148,149</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="27" unitid="11" objectid="4">
        <description>1-местная 1 класса</description>
        <details></details>
        <rooms>10</rooms>
        <location places="1" addplaces="0" childplaces="0" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="00001" add="00000" child="00000"/>
    </apartment>
    <apartment id="38" unitid="14" objectid="4">
        <description>2-местные 3А класса 2-ярусные</description>
        <details></details>
        <rooms>120,122,124,126,128,130,132,134,136,138,140</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="11111" add="00000" child="01100"/>
    </apartment>
    <apartment id="42" unitid="14" objectid="4">
        <description>2-х местные 3А класс без раковины</description>
        <details></details>
        <rooms>131,133,135</rooms>
        <location places="2" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="false"/>
        <dayweek_price value="false"/>
        <ages main="11111" add="00000" child="01100"/>
    </apartment>
    <apartment id="37" unitid="13" objectid="4">
        <description>4-местные 2Б класса</description>
        <details></details>
        <rooms>80,81,82,83,84,85,86,87</rooms>
        <location places="4" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="true"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="40" unitid="14" objectid="4">
        <description>4-местные 3 класса</description>
        <details></details>
        <rooms>121,123,125,127,129,137,139,141,143,145,147</rooms>
        <location places="4" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="true"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
    <apartment id="33" unitid="12" objectid="4">
        <description>4-местные 2В класса 2-ярусные</description>
        <details></details>
        <rooms>53,57</rooms>
        <location places="4" addplaces="0" childplaces="1" />
        <wholly value="false"/>
        <regular value="false"/>
        <lock_possible value="true"/>
        <dayweek_price value="false"/>
        <ages main="00111" add="00000" child="01100"/>
    </apartment>
</apartments>
</selena>

Загрузчик
+
Dim objBL 
Set objBL = CreateObject("SQLXMLBulkLoad.SQLXMLBulkload.4.0")
objBL.ConnectionString = "provider=SQLOLEDB;data source=.\moretravel;database=tempdb;integrated security=SSPI"
objBL.ErrorLogFile = "error.log"

'objBL.CheckConstraints=true
'objBL.XMLFragment = True
objBL.SchemaGen = True
objBL.SGDropTables = True

objBL.Execute "apartmentlist_Schema.xsd", "apartmentlist_4.xml"
Set objBL = Nothing


1.
Вопрос: какого хрена таблица xml_apartmentsages создается, но ключ НЕ создается?
<xsd:element name="ages" maxOccurs="1"
sql:relation="xml_apartmentsages"
sql:key-fields="partnerId objectid unitid id"
sql:relationship="ApartmentsAges"
>
Хотя там создается еще парочка аналогичных таблиц - и ключевые поля на месте.

2. Почему, если я убираю из этого же элемента аттрибуты
<xsd:attribute name="id" type="xsd:int" use="required" />
<xsd:attribute name="unitid" type="xsd:int" use="required" />
<xsd:attribute name="objectid" type="xsd:int" use="required" />
они загружаются как nvarchar(40), вместо int?

Не то чтоб это не давало жить, но обидно...
Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )

SqlXmlBulkLoad 4.0 SP1
30 ноя 15, 07:56    [18489833]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить