Невозможно создать таблицы после использования конвертирования для передачи данных из XML в SQL Server

Question

Невозможно создать таблицы после использования конвертирования для передачи данных из XML в SQL Server

Я немного новичок в этом, поэтому, пожалуйста, потерпите меня. Я пытаюсь сгенерировать таблицы из XML-документа. Из-за содержимого XML-документа мне пришлось использовать метод convert, с которым я не очень хорошо знаком. В результате я получаю ошибку. Я не уверен, почему, хотя. Я использую Microsoft SQL Server 2016.

Вот мой запрос:

WITH XmlFile (Contents) AS 
(
    SELECT 
        CONVERT (XML, BulkColumn,2)
    FROM 
        OPENROWSET (BULK 'C:\Users\Owner\Documents\congress\House votes\114 congress 2015\passage\roll705.xml', SINGLE_BLOB) AS roll705
)
SELECT *
FROM XmlFile
GO

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, xmlfile

SELECT *
FROM OPENXML (@hdoc, '/rollcall-vote/vote-metadata', 1)
WITH (
    congress tinyint,
    [session] char(3),
    chamber varchar(40),
    [rollcall-num] smallint,
    [legis-num] varchar(20),
    [vote-question] varchar(1000),
    [vote-result] varchar (20),
    [action-date] date,
    [vote-desc] varchar(1000)
)

EXEC sp_xml_removedocument @hdoc

Вот детская версия моего XML-документа

<?xml-stylesheet type="text/xsl" href="http://clerk.house.gov/evs/vote.xsl"?>
<rollcall-vote>
  <vote-metadata>
    <majority>R</majority>
    <congress>114</congress>
    <session>1st</session>
    <chamber>U.S. House of Representatives</chamber>
    <rollcall-num>705</rollcall-num>
    <legis-num>H R 2029</legis-num>
    <vote-question>On Concurring in Senate Amdt with Amdt Specified in Section 3(a) of H.Res. 566</vote-question>
    <vote-type>YEA-AND-NAY</vote-type>
    <vote-result>Passed</vote-result>
    <action-date>18-Dec-2015</action-date>
    <action-time time-etz="09:49">9:49 AM</action-time>
    <vote-desc>Making appropriations for military construction, the Department of Veterans Affairs, and related agencies for the fiscal year ending September 30, 2016, and for other purposes</vote-desc>
</rollcall-vote>

картина моей ошибки

ОБНОВИТЬ

Вот где я сейчас нахожусь:

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' +  CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[legislator],[state],[party],[vote],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],)
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''../maority[1]'',N''nvarchar(max)'') 
          ,v.value(N''../congress[1]'',N''int'') 
          ,v.value(N''../session[1]'',N''nvarchar(max)'') 
          ,v.value(N''../chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''../rollcall-num[1]'',N''int'') 
          ,v.value(N''../legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''../vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''../vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''../vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''../action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''../action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''../vote-desc[1]'',N''nvarchar(max)'') 
          ,@xml
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

это скриншот моих данных в моей папке

ОБНОВИТЬ

Это мой запрос.

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' + REPLACE(STR(@Counter,3),' ','0') + CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],)
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''majority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
          ,@xml
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH print 'error'
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

ОБНОВИТЬ

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' + REPLACE(STR(@Counter,3),' ','0') + CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],)
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''majority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
          ,v.value(N''sourceXML[1]'',N''XML)'')
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH print 'error'
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

ОБНОВИТЬ

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' + REPLACE(STR(@Counter,3),' ','0') + CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],)
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''majority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
          ,@xml
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH print ERROR_MESSAGE()
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

НОВАЯ ОШИБКА

Обновить

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' + REPLACE(STR(@Counter,3),' ','0') + CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],[sourceXML])
    SELECT ' +
         ',v.value(N''majority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
          ,@xml
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH print ERROR_MESSAGE()
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

ДРУГОЕ ОБНОВЛЕНИЕ

CREATE TABLE [dbo].[staagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL,
    [sourceXML] [XML] NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<800
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Users\Owner\Documents\congress\House votes\114 congress 2015\Passage\roll' + REPLACE(STR(@Counter,3),' ','0') + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.staagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc],[sourceXML])
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''majority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
          ,@xml
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH print ERROR_MESSAGE()
END CATCH;
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.staagingTable;
GO
DROP TABLE dbo.staagingTable;

На картинке ниже вы можете увидеть ошибку. Файлы, которые не существуют, имеют то же самое старое сообщение массового файла, не найденное. Но файлы, которые существуют (например, 006, 012 и 014), имеют другое сообщение.

1

sql sql-server xml data-conversion sql-convert

Источник

user7595443 21 фев '17 в 19:57

1 ответ

Решение

Другие вопросы по тегам sql sql-server xml data-conversion sql-convert

user5089204 21 фев '17 в 22:25 2017-02-21 22:25 · Accepted Answer · 2017-02-21 22:25

В качестве сути всех ваших пройденных вопросов я выбираю

В одном каталоге много XML-файлов (ну, много в одном и много в другом)
Все файлы имеют одинаковую структуру
Вы хотите прочитать их в таблицу

Мои предположения (которые вы должны адаптировать к вашим потребностям)

Имена являются вычислимыми (в моем случае "xml1", "xml2", "xml3"...)
Для моего тестового сценария я создал три файла XML (называемых "xml1.xml", "xml2.xml" и "xml3.xml" с содержимым вашей детской версии. Поэтому цикл останавливается на 4

Попробуй это

Я создаю промежуточную таблицу с подходящей структурой (не беспокойтесь о преобразовании даты и времени на этом первом шаге!)
Я использую счетчик для вычисления имени файла в цикле
Я использую WHILE петля
В этом цикле полный оператор создается динамически. Он будет читать ваш XML из файла в переменную и помещать все значения в таблицу.
С EXEC эта команда выполнена
Проверьте результат с помощью простого SELECT

Это код

CREATE TABLE [dbo].[stagingTable](
    [Counter] INT NOT NULL,
    [majority] [nvarchar](max) NULL,
    [congress] [int] NULL,
    [session] [nvarchar](max) NULL,
    [chamber] [nvarchar](max) NULL,
    [rollcall-num] [int] NULL,
    [legis-num] [nvarchar](max) NULL,
    [vote-question] [nvarchar](max) NULL,
    [vote-type] [nvarchar](max) NULL,
    [vote-result] [nvarchar](max) NULL,
    [action-date] [nvarchar](max) NULL,
    [action-time] [nvarchar](max) NULL,
    [vote-desc] [nvarchar](max) NULL
);
GO

DECLARE @Counter INT=1;
DECLARE @command VARCHAR(MAX);

WHILE @Counter<4
BEGIN
    SET @command=
    '
    DECLARE @xml XML=
    (
    SELECT BulkColumn
    FROM OPENROWSET (BULK ''C:\Stackru\xml' +  CAST(@Counter AS VARCHAR(10)) + '.xml'', SINGLE_BLOB) AS c
    );

    INSERT INTO dbo.stagingTable(Counter,majority,congress,[session],chamber
              ,[rollcall-num],[legis-num],[vote-question],[vote-type]
              ,[vote-result],[action-date],[action-time],[vote-desc])
    SELECT ' +  CAST(@Counter AS VARCHAR(10)) + 
         ',v.value(N''maority[1]'',N''nvarchar(max)'') 
          ,v.value(N''congress[1]'',N''int'') 
          ,v.value(N''session[1]'',N''nvarchar(max)'') 
          ,v.value(N''chamber[1]'',N''nvarchar(max)'')
          ,v.value(N''rollcall-num[1]'',N''int'') 
          ,v.value(N''legis-num[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-question[1]'',N''nvarchar(max)'')
          ,v.value(N''vote-type[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-result[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-date[1]'',N''nvarchar(max)'') 
          ,v.value(N''action-time[1]'',N''nvarchar(max)'') 
          ,v.value(N''vote-desc[1]'',N''nvarchar(max)'') 
    FROM @xml.nodes(N''/rollcall-vote/vote-metadata'') AS A(v);
    ';
    EXEC(@command);
    SET @Counter=@Counter+1;
END
SELECT * FROM dbo.stagingTable;
GO
DROP TABLE dbo.stagingTable;

ОБНОВИТЬ

Если имена файлов вычислимы, но есть пробелы, просто измените простое EXEC(@cmd) к этому:

BEGIN TRY
EXEC(@command);
END TRY
BEGIN CATCH
--Might make sense to write some error meta data into a log table
END CATCH

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