Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Быстрый Insert данных из XML  [new]
memcpy
Member

Откуда:
Сообщений: 8
Добрый день,
клиентская программа парсит XML и заливает в базу данных.
+ XML
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Root>
  <Nodes>
    <Node Name="" Value="">
      <Nodes>
        <Node Name="Protocol 1" Value="">
          <Fields>
            <Field Name="Parametr 1" Value="Value 1" />
          </Fields>
          <Nodes>
            <Node Name="Protocol 1.1" Value="">
              <Nodes>
                <Node Name="Protocol 1.1.1" Value="">
                  <Nodes>
                    <Node Name="Protocol 1.1.1.1" Value="">
                      <Fields>
                        <Field Name="Parametr 1" Value="Value 1" />
                        <Field Name="Parametr 2" Value="Value 2" />
                        <Field Name="Parametr 3" Value="Value 3" />
                      </Fields>
                      <Nodes>
                        <Node Name="Protocol 1.1.1.1.1" Value="">
                          <Fields>
                            <Field Name="Parametr 1" Value="Value 1" />
                          </Fields>
                        </Node>
                      </Nodes>
                    </Node>
                    <Node Name="Protocol 1.1.1.2" Value="">
                      <Fields>
                        <Field Name="Parametr 1" Value="Value 1" />
                        <Field Name="Parametr 2" Value="Value 2" />
                        <Field Name="Parametr 3" Value="Value 3" />
                        <Field Name="Parametr 4" Value="Value 4" />
                        <Field Name="Parametr 5" Value="Value 5" />
                      </Fields>
                    </Node>
                  </Nodes>
                </Node>
              </Nodes>
            </Node>
          </Nodes>
        </Node>
      </Nodes>
    </Node>
  </Nodes>
</Root>

+ Таблицы БД
CREATE TABLE [dbo].[tbProtocols] (
    [ID]       BIGINT        IDENTITY (1, 1) NOT NULL,
    [ParentID] BIGINT        NULL,
    [Name]     NVARCHAR (64) NULL,
    CONSTRAINT [PK_tbProtocols] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_tbProtocols_tbProtocols] FOREIGN KEY ([ParentID]) REFERENCES [dbo].[tbProtocols] ([ID])
);

CREATE TABLE [dbo].[tbParametrs] (
    [ID]         BIGINT        IDENTITY (1, 1) NOT NULL,
    [ProtocolID] BIGINT        NOT NULL,
    [Name]       NVARCHAR (64) NOT NULL,
    [Value]      NVARCHAR (64) NOT NULL,
    CONSTRAINT [PK_tbParametrs] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_tbParametrs_tbProtocols] FOREIGN KEY ([ProtocolID]) REFERENCES [dbo].[tbProtocols] ([ID])
);


Все бы хорошо, но скорость не впечатляет, нужно заливать < 100 Mb файлы xml в которых по неск. млн строк
Будет ли быстрее, если парсить на сервере через OPENXML и как составить запрос, учитывая что Protocol является иерархической (Id, ParentId)?
Вобщем нужно xml раскидать на 2 таблицы

IDParentIDName
1NULL
21Protocol 1
32Protocol 1.1
43Protocol 1.1.1
54Protocol 1.1.1.1
65Protocol 1.1.1.1.1
74Protocol 1.1.1.2

IDProtocolIDNameValue
12Parametr 1Value 1
25Parametr 1Value 1
35Parametr 2Value 2
45Parametr 3Value 3
56Parametr 1Value 1
67Parametr 1Value 1
77Parametr 2Value 2
87Parametr 3Value 3
97Parametr 4Value 4
107Parametr 5Value 5


P.S. или лучше будет не через OPENXML делать, а на клиенте распарсить xml до CSV и дальше BulkInsert всего файла?
27 апр 15, 14:51    [17569635]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
invm
Member

Откуда: Москва
Сообщений: 9402
https://msdn.microsoft.com/en-us/library/ms171993.aspx
27 апр 15, 15:05    [17569748]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
VAlexey
Member

Откуда:
Сообщений: 130
Если клиентское приложение на .net, то можно еще использовать это: https://msdn.microsoft.com/ru-ru/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx
27 апр 15, 16:46    [17570765]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
nizamovich
Member

Откуда: Москва
Сообщений: 116
memcpy,

написать CLR, парсить на стороне, и возвращать в табличном виде.
27 апр 15, 16:54    [17570880]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
memcpy
Member

Откуда:
Сообщений: 8
VAlexey, клиент на С#, но
SqlBulkCopy не подойдет, т.к. необходимо генерить первичные ключи на сервере (для обеспечения уникальности), а использовать guid в качестве ключа повлечет увеличение времени заливки данных.
Буду разбираться как XSD + SQLXMLBulkLoad сделать
27 апр 15, 16:58    [17570931]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
nizamovich
Member

Откуда: Москва
Сообщений: 116
memcpy,
а, не обратил внимание, что работа идет на стороне клиента.


nizamovich
memcpy,

написать CLR, парсить на стороне, и возвращать в табличном виде.
27 апр 15, 16:59    [17570938]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
VAlexey
Member

Откуда:
Сообщений: 130
memcpy,

С помощью SqlBulkCopy можно загрузить данные во временные таблицы #, а оттуда уже вставить в нужные реальные. Чтобы связать вставленные строки с присвоенными identity, в любом случае понадобится какой-то набор уникальных полей (если не рассматривать вставку по 1 строке).
Если значение Node/@Name уникально, то можно по нему.
27 апр 15, 18:24    [17571543]     Ответить | Цитировать Сообщить модератору
 Re: Быстрый Insert данных из XML  [new]
memcpy
Member

Откуда:
Сообщений: 8
Сделал схему
+ XMLSchema.xsd
<xsd:schema attributeFormDefault="unqualified"
    elementFormDefault="qualified"
    xmlns:xsd="http://www.w3.org/2001/XMLSchema"
    xmlns:sql="urn:schemas-microsoft-com:mapping-schema">

  <xsd:annotation>
    <xsd:appinfo>
      <sql:relationship name="tbProtocols_tbProtocols"
      parent="tbProtocols"
      parent-key="ID"
      child="tbProtocols"
      child-key="ParentID" />
      <sql:relationship name="tbParametrs_tbProtocols"
      parent="tbProtocols"
      parent-key="ID"
      child="tbParametrs"
      child-key="ProtocolID" />
    </xsd:appinfo>
  </xsd:annotation>

  <xsd:element name="Root" sql:is-constant="1">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="Nodes" type="ProtocolType" minOccurs="1" maxOccurs="1"/>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>

  <xsd:complexType name="ParametrType">
    <xsd:sequence>
      <xsd:element name="Field" sql:relation="tbParametrs" sql:relationship="tbParametrs_tbProtocols" minOccurs="1" maxOccurs="unbounded">
        <xsd:complexType>
          <xsd:attribute name ="Name" type="xsd:string" />
          <xsd:attribute name ="Value" type="xsd:string" />
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>

  <xsd:complexType name="ProtocolType">
    <xsd:sequence>
      <xsd:element name="Node" sql:relation="tbProtocols" sql:relationship="tbProtocols_tbProtocols" minOccurs="1" maxOccurs="1">
        <xsd:complexType>
          <xsd:sequence>
            <xsd:element name="Fields" type="ParametrType" minOccurs="0" maxOccurs="1"/>
            <xsd:element name="Nodes" type="ProtocolType" minOccurs="0" maxOccurs="1"/>
          </xsd:sequence>
          <xsd:attribute name ="Name" type="xsd:string" />
          <xsd:attribute name ="Value" type="xsd:string" fixed="" />
        </xsd:complexType>
      </xsd:element>
    </xsd:sequence>
  </xsd:complexType>

</xsd:schema>

При вызове
                SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class objBL = new SQLXMLBULKLOADLib.SQLXMLBulkLoad4Class();
                objBL.ConnectionString = "provider=SQLOLEDB;data source=.;database=db2;integrated security=SSPI";
                objBL.ErrorLogFile = "error.xml";
                objBL.KeepIdentity = false;
                objBL.Execute("XMLSchema.xsd", "file1.xml");


Выдает исключение
Schema: the parent/child table of the relationship on 'Node' does not match.


Требуется помощь, гдето в схеме намудрил
28 апр 15, 15:57    [17576381]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить