Проблема с XSD, импортом XML в SQL - текстовый узел не разрешен в этом месте

Я создаю свой первый XSD, так как у меня есть XML-файл размером 4 МБ, который мне нужно разобрать на SQL, а обработка чего-то такого большого занимает слишком много времени с использованием нетипизированного XML (я сдался и отменил запрос через час).

XML-файл, который у меня есть, имеет следующий формат (для каждого продукта есть больше элементов, но я сократил его и создал тестовый XML-файл, пока не понял правильно):

 <ITEMS>
    <CREATED value="Wed May 2 9:40:38 BST 2012">
        <PRODUCT ITEM="0001">
            <MODEL>MODELNO1</MODEL>
            <BARCODE>5550204425</BARCODE>
            <TITLE>Item 1 Title</TITLE>
        </PRODUCT>
        <PRODUCT ITEM="0002">
            <MODEL>MODELNO2</MODEL>
            <BARCODE>52614343433</BARCODE>
            <TITLE>Item 2 Title</TITLE>         
        </PRODUCT>
        <PRODUCT ITEM="0003">
            <MODEL>MODELNO3</MODEL>
            <BARCODE>32563533</BARCODE>
            <TITLE>Item 3 Title</TITLE>         
        </PRODUCT>
        <PRODUCT ITEM="0004">
            <MODEL>MODELNO4</MODEL>
            <BARCODE>65135647582</BARCODE>
            <TITLE>Item 4 Title</TITLE>         
        </PRODUCT>
        <PRODUCT ITEM="0005">
            <MODEL>MODELNO5</MODEL>
            <BARCODE>65874112</BARCODE>
            <TITLE>Item 4 Title</TITLE>         
        </PRODUCT>
    </CREATED>
   </ITEMS>

Этот XML-файл автоматически генерируется внешней системой-поставщиком, и у меня нет другого выбора, кроме как работать с ним в текущем формате.

Я создал для него эту схему:

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="ITEMS">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="CREATED">
                    <xs:complexType>
                        <xs:sequence>
                            <xs:element name="PRODUCT" maxOccurs="unbounded">
                                <xs:complexType>
                                    <xs:sequence>
                                        <xs:element name="MODEL" type="xs:string" maxOccurs="unbounded" />
                                        <xs:element name="BARCODE" type="xs:string" maxOccurs="unbounded" />
                                        <xs:element name="TITLE" type="xs:string" maxOccurs="unbounded" />
                                    </xs:sequence>
                                </xs:complexType>
                            </xs:element>
                        </xs:sequence>
                    </xs:complexType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>

В SQL я сначала создал коллекцию схем, например:

IF EXISTS ( SELECT * FROM sys.xml_schema_collections where [name] = 'MyXmlSchema')
DROP XML SCHEMA COLLECTION [MyXmlSchema]
GO

DECLARE @MySchema XML
SET @MySchema = 
(
    SELECT * FROM OPENROWSET
    (
        BULK 'C:\test\schema2.xsd', SINGLE_CLOB 
    ) AS xmlData
)

CREATE XML SCHEMA COLLECTION [MyXmlSchema] AS @MySchema 
GO

Затем я создал таблицу на основе схемы:

CREATE TABLE [dbo].[XMLProds] (
    [MODEL] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL,
    [EAN] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL,
    [NAME] xml(CONTENT dbo.[MyXmlSchema]) NOT NULL
)

И, наконец, проверил XML:

DECLARE @x2 XML ([MyXmlSchema])
SELECT @x2 = '<copied the code from the test XML file and pasted here>'

Проверке не понравилось значение даты в поле «СОЗДАН», без которого я могу жить, поскольку оно объявлено только один раз и может быть легко удалено. Но ему также не понравилось значение «ПУНКТ» в каждом из полей продукта, что не является проблемой. 2. Это нельзя не учитывать, так как оно присутствует в каждом отдельном предмете (всех 2-3 тысячах). Есть ли способ обойти это?

Просто для того, чтобы продолжить, я удалил ненужные значения из тестового XML, и проверка прошла. Затем я выполнил этот оператор, пытаясь заполнить таблицу:

INSERT INTO XMLProds (MODEL, BARCODE, TITLE)

    SELECT X.product.query('MODEL').value('.', 'VARCHAR(20)'),
           X.product.query('BARCODE').value('.', 'VARCHAR(50)'),
           X.product.query('TITLE').value('.', 'VARCHAR(150)')

FROM (
    SELECT CAST(x AS XML)
    FROM OPENROWSET(BULK 'C:\test\Products2test.xml', SINGLE_BLOB) AS T(x)) AS T(x)
    CROSS APPLY x.nodes('/ITEMS/CREATED/PRODUCT') AS X(product);

.. но столкнулся со следующей ошибкой:

Сообщение 6909, уровень 16, состояние 1, строка 21 Проверка XML: текстовый узел не разрешен в этом месте, тип был определен с содержимым только элемента или с простым содержимым. Расположение: /

Любая помощь с тем, где я ошибаюсь, будет принята с благодарностью! Заранее спасибо.


person odinel    schedule 25.05.2012    source источник


Ответы (1)


Я бы начал с действительного XSD. Вам не хватает атрибутов, поэтому у вас возникают проблемы с нежелательным контентом.

<?xml version="1.0" encoding="utf-8"?>
<!--XML Schema generated by QTAssistant/XML Schema Refactoring (XSR) Module (http://www.paschidev.com)-->
<xsd:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <xsd:element name="ITEMS">
    <xsd:complexType>
      <xsd:sequence>
        <xsd:element name="CREATED">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element maxOccurs="unbounded" name="PRODUCT">
                <xsd:complexType>
                  <xsd:sequence>
                    <xsd:element name="MODEL" type="xsd:string" />
                    <xsd:element name="BARCODE" type="xsd:unsignedLong" />
                    <xsd:element name="TITLE" type="xsd:string" />
                  </xsd:sequence>
                  <xsd:attribute name="ITEM" type="xsd:unsignedByte" use="required" />
                </xsd:complexType>
              </xsd:element>
            </xsd:sequence>
            <xsd:attribute name="value" type="xsd:string" use="required" />
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Если это все еще не работает, дайте мне знать.

person Petru Gardea    schedule 26.05.2012
comment
Спасибо за ваш ответ. Я изменил атрибут ITEM на строку, и XML прошел проверку, так что вы решили эту проблему. Все еще получаю ту же ошибку SQL. Тем не менее, это сузило круг, и мне есть над чем поработать, так что спасибо. - person odinel; 26.05.2012
comment
Я бы использовал валидатор XML (Visual Studio имеет встроенный редактор XML, который поддерживает проверку XSD), чтобы сначала проверить XML против вашего предполагаемого XSD; пока игнорируйте аспект SQL. Как только вы успешно проверите свой XML независимо от вашей инфраструктуры обработки (и докажете, что XSD правильный), попробуйте еще раз. В противном случае вы зря теряете время. 4 МБ не так уж и много; Если бы файл имел 4 ГБ, я бы все равно сделал то же самое, за исключением того, что это было бы скорее программно, после чтения потока и независимой проверки каждого узла (записи). - person Petru Gardea; 26.05.2012