Как использовать OPENXML для загрузки данных XML в существующую таблицу SQL?

Я новичок в OPENXML. Но я пытаюсь загрузить файл.XML в таблицу SQL, которую я создал для этого. Я не получаю никаких ошибок с этим кодом, но он также не вставляет никаких записей. Это таблица, которую я создал в SQL Server 2008:

CREATE TABLE HOMEROOM(
HOMEROOM_TEACHER INT,
HOMEROOM_NUMBER INT,
ENTITY_ID INT)

И это код T-SQL, который я пытаюсь выполнить:

DECLARE @idoc int
DECLARE @xmlDocument varchar(MAX)
DECLARE @Status INT

SET @xmlDocument ='
<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
  <s:Schema id="RowsetSchema">
    <s:ElementType name="row" content="eltOnly">
      <s:AttributeType name="c0" rs:name="HOMEROOM-TEACHER" rs:number="1" rs:nullable="true">
        <s:datatype dt:type="int" dt:maxLength="4" rs:precision="10" rs:fixedlength="true" />
      </s:AttributeType>
      <s:AttributeType name="c1" rs:name="HOMEROOM-NUMBER" rs:number="2">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" />
      </s:AttributeType>
      <s:AttributeType name="c2" rs:name="ENTITY-ID" rs:number="3">
        <s:datatype dt:type="string" rs:dbtype="str" dt:maxLength="10" rs:maybenull="false" />
      </s:AttributeType>
      <s:extends type="rs:rowbase" />
    </s:ElementType>
  </s:Schema>
  <rs:data>
    <z:row c0="22943" c1="101" c2="055" />
    <z:row c0="22929" c1="102" c2="055" />
    <z:row c0="22854" c1="103" c2="055" />
    <z:row c0="22908" c1="104" c2="055" />
    <z:row c0="22881" c1="105" c2="055" />
<z:row c0="22926" c1="Gym2" c2="055" />
<z:row c0="22935" c1="Gym3" c2="055" />
  </rs:data>
</xml>
'
EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, @xmlDocument
SELECT 'sp_xml_preparedocument status=',@Status

select *
FROM   OPENXML (@idoc, '/xml/',1)
         WITH    (
            HOMEROOM_TEACHER          INT    '@C0'
            ,HOMEROOM_NUMBER          VARCHAR(10) '@C1'
            ,ENTITY_ID          VARCHAR(10) 'C2'
                )

--sp_xml_removedocument @idoc

SELECT * FROM HOMEROOM

Но после этого я получаю 0 строк, добавленных в HOMEROOM. Любые предложения о том, как сделать эту работу?

Когда я выполняю выше, я получаю ошибку: (затронуты 1 строка) Msg 245, Уровень 16, Состояние 1, Строка 627 Преобразование не удалось при преобразовании значения nvarchar 'Gym2' в тип данных int.

Источник

3 ответа

Решение

Попробуй это:

EXEC @Status = sp_xml_preparedocument @idoc OUTPUT, 
@xmlDocument, '<xml xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882" 
       xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882" 
       xmlns:rs="urn:schemas-microsoft-com:rowset" 
       xmlns:z="#RowsetSchema"/>' 
SELECT 'sp_xml_preparedocument status=',@Status 

SELECT * 
FROM OPENXML (@idoc, '/xml/rs:data/z:row',1) 
WITH ( 
   HOMEROOM_TEACHER   INT    '@c0' 
  ,HOMEROOM_NUMBER    INT    '@c1' 
  ,ENTITY_ID          INT    '@c2' 
)

Я сделал несколько вещей:

  1. В качестве третьего параметра добавлено объявление пространства имен в sp_xml_preparedocument.
  2. Изменен раздел xpath с '/xml/' на '/xml/rs:data/z:row', чтобы указать правильную позицию и пространства имен в документе XML
  3. Изменены переменные @C в нижний регистр (@c)

Результаты были:

HOMEROOM_TEACHER HOMEROOM_NUMBER ENTITY_ID
---------------- --------------- -----------
22943            101             55
22929            102             55
22854            103             55
22908            104             55
22881            105             55

К вашему сведению, информацию об использовании OPENXML с пространствами имен можно найти здесь.

Источник

Прежде всего, я бы использовал SQL Server 2005 XQuery поверх OPENXML - мне кажется, проще и чище.

Во-вторых - не совсем понятно, какие элементы или атрибуты вы хотите извлечь....

Третье: вы игнорируете пространства имен XML, поэтому ничего не работает... они существуют по какой-то причине, и вам нужно обратить на них внимание!

Итак, я попробовал что-то вроде этого здесь:

DECLARE @input XML = '.....'

;WITH XMLNAMESPACES('urn:schemas-microsoft-com:rowset' AS rs, '#RowsetSchema' AS z)
SELECT
    Nodes.Attr.value('(@c0)[1]', 'INT') AS 'HomeroomTeacher',
    Nodes.Attr.value('(@c1)[1]', 'INT') AS 'HomeroomNumber',
    Nodes.Attr.value('(@c2)[1]', 'INT') AS 'EntityID'
FROM
    @input.nodes('/xml/rs:data/z:row') AS NOdes(Attr)

и я получаю вывод:

HomeroomTeacher  HomeroomNumber  EntityID
   22943              101           55
   22929              102           55
   22854              103           55
   22908              104           55
   22881              105           55

Возможно, это еще не совсем то, что вы ищете, но это может быть отправной точкой!

Я сделал:

  • определить соответствующие пространства имен XML rs: а также z: используя это WITH XMLNAMESPACES сооружать
  • создал "псевдотаблица" Nodes с псевдоколонкой Attr который в основном имеет одну строку XML для каждого элемента, который соответствует этому выражению XPath
  • Затем я берусь за эти строки в псевдотаблице и могу извлечь нужную информацию, которая мне нужна.
Источник

Вам нужно добавить INSERT INTO HOMEROOM (HOMEROOM_TEACHER, HOMEROOM_NUMBER, ENTITY_ID) выше вашего выбора и измените ваш SELECT на SELECT HOMEROOM_TEACHER ,HOMEROOM_NUMBER ,ENTITY_ID,

Источник
Другие вопросы по тегам