Как использовать 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 ряд) Сообщение 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'
)
Я сделал несколько вещей:
- Добавил объявление пространства имен как третий параметр sp_xml_preparedocument.
- Изменен раздел xpath с '/xml/' на '/xml/rs:data/z:row', чтобы указать правильную позицию и пространства имен в XML-документе
- Изменены переменные @C на нижний регистр (@c)
Результаты были:
HOMEROOM_TEACHER HOMEROOM_NUMBER ENTITY_ID
---------------- --------------- -----------
22943 101 55
22929 102 55
22854 103 55
22908 104 55
22881 105 55
FYI, информацию об использовании 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:, используя конструкцию thteWITH XMLNAMESPACES - , созданную «псевдо- table "
Узлыс псевдостолбцомAttr, который в основном имеет одну строку XML для каждого элемента, который соответствует этому выражению XPath - Затем я обращаюсь к этим строкам в псевдотаблице и Я могу извлечь нужную мне информацию
Вам нужно добавить INSERT INTO HOMEROOM (HOMEROOM_TEACHER, HOMEROOM_NUMBER, ENTITY_ID) над вашим select и изменить ваш SELECT на SELECT HOMEROOM_TEACHER ,HOMEROOM_NUMBER ,ENTITY_ID.