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

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

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.


tsql openxml
person salvationishere    schedule 05.08.2010    source источник

Ответы (3)


arrow_upward
1
arrow_downward

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

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 с пространствами имен можно найти здесь.

person 8kb    schedule 06.08.2010
comment
Мне нравится это решение! Да, ты поймал все мои ошибки. Теперь это работает для меня. Большое спасибо! Ты замечательный! - person salvationishere; 06.08.2010
comment
Эй, я ошибся. Он не добавил эти данные в мою таблицу, потому что вместо этого у меня есть два поля varchar. Итак, я получаю сообщение об ошибке выше. Я обновил свое описание также новыми данными. Не могли бы вы еще раз взглянуть? - person salvationishere; 06.08.2010
comment
Неважно, мне просто нужно было воссоздать таблицу с помощью VARCHARS. Спасибо. - person salvationishere; 06.08.2010

arrow_upward
2
arrow_downward

Прежде всего, я бы использовал 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
  • Затем я просматриваю эти строки в псевдотаблице и могу извлечь нужные биты информации, которые мне нужны.
person marc_s    schedule 06.08.2010
comment
Спасибо, Марк, я не пробовал ваше решение, но похоже, что оно сработает хорошо. У меня есть данные, ориентированные на атрибуты. Раньше я не понимал, как объявлять пространства имен, но теперь понимаю. Знаете ли вы, что более эффективно на сервере? XQuery или OPENXML? - person salvationishere; 06.08.2010
comment
Прости, Марк, но я ошибся. У меня есть два значения VARCHAR и одно INT. Я обновил свое описание с сообщением об ошибке. Не могли бы вы еще раз взглянуть? - person salvationishere; 06.08.2010
comment
Неважно, мне просто нужно было воссоздать таблицу с помощью VARCHARS. Спасибо. - person salvationishere; 06.08.2010

arrow_upward
0
arrow_downward

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

person Tahbaza    schedule 06.08.2010