Unable to generate tables after using convert for data transfer from XML to SQL Server

Question

I am a little new at this so please bear with me. I am attempting to generate tables from an xml document. Due to some of the contents of the XML document I had to use the convert method, which I am not very familiar with. As a result I get an error. I am not sure why though. I am using Microsoft SQL Server 2016.

Here is my query:

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

Here is a baby version of my XML document

<?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>

a picture of my error

UPDATE

This is where I am at now:

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;

this is a screenshot of my data in my folder

UPDATE

This is my query.

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;

UPDATE

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;

UPDATE

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;

NEW ERROR

update

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;

ANOTHER UPDATE

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;

In the picture below you can see the error. The files that don't exist have the same old bulk file not found message. But the files that do exist (like 006, 012, and 014) have a different message.

You are reading the XML and writing it out with `SELECT` - but then follows a `GO`. After this you try to use `sp_xml_preparedocument ` on `xmlfile`. This cannot work the way you posted it... — Shnugo, Feb 21 '17 at 21:53
Your screenshot shows, that your file names are padded with zeros (*roll006* instead of *roll6*). You can pad a number with `REPLACE(STR(@Counter,3),' ','0')`. This will return `006` — Shnugo, Feb 21 '17 at 23:31
Here: `FROM OPENROWSET (BULK ''C:\StackOverflow\xml' + REPLACE(STR(@Counter,3),' ','0') + '.xml'', SINGLE_BLOB) AS c` — Shnugo, Feb 21 '17 at 23:39
Furthermore: Your staging table, the list of columns after `INSERT INTO` and the list of values after `SELECT` must be the same. This is not correct at the moment... — Shnugo, Feb 21 '17 at 23:40
Set this between `BEGIN CATCH` and `END CATCH`: `PRINT ERROR_MESSAGE();` You will get a speaking error message... — Shnugo, Feb 21 '17 at 23:42
shall I remove action-date and action-time in addition to removing state, legislator, party, and vote? — ana ng, Feb 21 '17 at 23:45
I thought I would need some because the data for state, legislator, vote, and party are lower in the xpath compared to the other data. — ana ng, Feb 21 '17 at 23:46
Please try to solve one thing after the other. This issue is *How do I read file after a file into a staging table?* The next issue is *How do I read all of my values out of **one** XML?* (the others will work the same way). The second you should not try with dynamically created SQL but with simple direct call. When this works, combine both. — Shnugo, Feb 22 '17 at 00:26
Btw: *are lower in the xpath*? Your `../` is **backward navigation** meaning *going up the tree*... — Shnugo, Feb 22 '17 at 00:27
I understand. I am currently just trying to work with the values we started out with. I updated my question to reflect this in the most recent update. Still for some reason my only result is the picture of the empty table. — ana ng, Feb 22 '17 at 00:33
Try without the `../`. Here `FROM @xml.nodes(N''/rollcall-vote/vote-metadata'')` you are walking down to ``. Why do you want to move back to ``? — Shnugo, Feb 22 '17 at 00:42
Did you check for messages created with `PRINT`? Are there errors? — Shnugo, Feb 22 '17 at 00:44
Hey, really... I told you to check this... At `,[vote-desc],)` you obviously wanted to place the additional column name `sourceXML` but there's nothing but a comma... — Shnugo, Feb 22 '17 at 00:46
Once again: With `PRINT ERROR_MESSAGE();` you will get a speaking message, if something gets wrong... — Shnugo, Feb 22 '17 at 00:48
how would I check PRINT? also the ../ has been removed (I accidentally copy pasted the wrong query into my most recent update, which I now fixed). Also for sourceXML would that be done just lke the other columns? — ana ng, Feb 22 '17 at 00:50
check code coloring highlighting incorrect ' placement: SELECT ' + CAST(@Counter AS VARCHAR(10)) + — Mitch Wheat, Feb 22 '17 at 00:51
@anang After `INSERT INTO` there is a list of columns. The last one should be `sourceXML` but still there is just an empty comma???. Within SSMS you have two tabs. One for the result, and one for messages. `PRINT` writes into the second tab. — Shnugo, Feb 22 '17 at 00:55
I have included the message now. It just say error repeatedly and ends with 0 rows affected — ana ng, Feb 22 '17 at 01:19
I see sourcexml just about the xpath at the end of the insert into clause — ana ng, Feb 22 '17 at 01:21
Here `BEGIN CATCH print 'error'` you print the word "error", nothing else... print the `ERROR_MESSAGE()`! And I can see still the same error as before: `,[vote-result],[action-date],[action-time],[vote-desc],)` at the end there is just a comma and the closing paranthesis. You are missing the last column name `sourceXML` there... — Shnugo, Feb 22 '17 at 01:27
And this `,v.value(N''sourceXML[1]'',N''XML)'')` will not return anything as the XML you want to write there is not buried within the XML you are reading from. Just place a `@xml` there... — Shnugo, Feb 22 '17 at 01:29
In the code you wrote [vote-result],[action-date], [action-time], [vote-desc] were present. are you saying I should remove them? and by a comma followed by a closing parenthesis do u man to say semicolon? Otherwise I don't know what comma and closing parenthesis you are referring to. Also I am not completely sure what you are saying I should put there. Btw I have now replaced the print error as per your instructions. — ana ng, Feb 22 '17 at 01:36
actually the error mentions the thing you are talking about. I just cant find it though — ana ng, Feb 22 '17 at 01:40
Oh I figured out what you at referring to! I feel so dumb. There is a new error now. I will post it. — ana ng, Feb 22 '17 at 01:45
@anang The error "Cannot bulk load..." just tells you, that the file with the given path does not exist. Do you get a result now? — Shnugo, Feb 22 '17 at 01:51
No result other htan the columns with no values. I am looking at the files right now though. They are real. I can click on them and see the xml files for myself. It thinks that I got the file path wrong but I could swear that I didn't. — ana ng, Feb 22 '17 at 01:54
Oh I think I understand what you mean now. This is that error catch that allows me to search large numbers of files even though they have gaps in them. I understand, but there are still no results generated. Interesstingly enough there is a strange pattern in file paths it says don't exist. I will post it now. — ana ng, Feb 22 '17 at 01:59
the message results are correct in saying that I don't have any of the files that it listed. But I have a feeling that it look for any of the files that I wanted it to look for. My file numbers go up to a maximum of three numerical digits. This message seems to indicate that it started searching at 4 numerical digits. also it is very weird about its progression. — ana ng, Feb 22 '17 at 02:05
Oh ana ng! Look at this: `REPLACE(STR(@Counter,3),' ','0') + CAST(@Counter AS VARCHAR(10))` If your counter is let's say 7, the first part will create `007` and then you will add the seven once again resulting in `0077`... I hope this comes to an end soon! — Shnugo, Feb 22 '17 at 02:11
I am not sure what the solution is then. Also since I have a 3 there doesn't that mean the search would start at 003 and then move on to 0033? That isn't what it does although it is doing something simlar — ana ng, Feb 22 '17 at 02:15
You do not need this `+ CAST(@Counter AS VARCHAR(10))` take it away! — Shnugo, Feb 22 '17 at 02:29
A general hint: Try to separate your issues: One issue - obviously! - is the generation of the files names. Take everthing else away and print nothing more than this. When this is OK, read the XML and insert nothing else then the XML *as-is*. When this is OK, read **just one** first-level value. When this is okay read more values. And so on... — Shnugo, Feb 22 '17 at 02:32
Another error results. I think removing it messed up the syntax a little. will show picture. — ana ng, Feb 22 '17 at 02:35
oddly enough it wont let me post a picture. But my error says Incorrect syntax near ','. — ana ng, Feb 22 '17 at 02:41
You took this `+ CAST(@Counter AS VARCHAR(10))` away, but in the wrong place: There, where the file name is created, it must be taken away. After the `SELECT` it must stay. And **very important!** You create the table `dbo.staagingTable` with `aa` but you insert into `dbo.stagingTable`. — Shnugo, Feb 22 '17 at 02:53
I have made the edits. There seems to be a new error as found in my update. — ana ng, Feb 22 '17 at 03:02
Is there a `DOCTYPE` involved? Hey, this question is far off what SO is meant to be! Please stop this now, please up-vote my answer and accept it, please up-vote - if you like this - my answers in your previous questions. And please try to separate your issues. Start new questions, where you solve one step after the other. About `DOCTYPE`: Read the XML into a string variable and strip everything away until the first *real* node. Then convert this to XML. — Shnugo, Feb 22 '17 at 03:09

Shnugo · Accepted Answer · 2017-02-21T23:03:18.507

As the essence of all your passed questions I pick

There are many xml files in the same directory (well, many in one and many in another)
All files have the same structure
You want to read them into a table

My Assumptions (which you have to adapt to your needs)

The names are computable (in my case "xml1", "xml2", "xml3" ...)
For my testscenario I created three XML files (called "xml1.xml", "xml2.xml" and "xml3.xml" with the content of your baby version. Therefore the loop stops at 4

Try this

I create a staging table with the fitting structure (do not bother about datetime conversion in this first step!)
I use a counter to compute the file's name within a loop
I use a WHILE loop
Within this loop the full statement is dynamically created. It will read your XML from the file into a variable and push all the values into the table.
With EXEC this command is executed
Check the result with a simple SELECT

This is the code

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:\StackOverflow\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;

UPDATE

If the file names are computeable, but there are gaps, just change the simple EXEC(@cmd) to this:

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

This will ignore all file-not-found errors...

How did you decide what would be null and what would be not null? — ana ng, Feb 21 '17 at 22:34
@anang A staging table should be as tolerant as possible. The `Counter` will be there for sure. All other columns might be missing in the XML. It could be better, to define all columns as `nvarchar(max)` (there are two `int` columns at the moment). You might create one additional XML column to insert the XML *as-is* for later reference. — Shnugo, Feb 21 '17 at 22:52
I do not believe that the names are computable. While they are numbered and progressively get larger they do skip some numbers here and there. — ana ng, Feb 21 '17 at 22:57
This additional XML column would contain all the data in my xml? — ana ng, Feb 21 '17 at 23:00
@anang, yes, just add a column `SourceXML XML NULL` to your staging table and add `,@xml` to the column list of the `SELECT`. This will write the full XML *as-is* into your staging table. **Don't forget to add `SourceXML` to the column names after `INSERT INTO` too...** — Shnugo, Feb 21 '17 at 23:04
Now if I have some more columns in my data (I didn't post the whole thing) I should alter the xpath so that it is all inclusive, add ../ accordingly, and change [1] into [2] for the added columns (since they are elements and not attributes) right? — ana ng, Feb 21 '17 at 23:08
@anang I've the feeling, that your ideas are not quite okay... My suggestion (to avoid chameleon questions and stick to SO's principle *one issue - one question*): Try this with your files and ignore other columns for the moment. If this works, please accept my answer to close this question. Please go back to your previous questions and accept the best answer (your choice!) there too. Then try with one XML how to read all the values. You might stumble into new issues. Start a new *specific* question for each new *specific* issue. Keep everything tiny... — Shnugo, Feb 21 '17 at 23:15
Okay. I have done all of that. I am getting a table but there are no values in it. I will update my post to show. — ana ng, Feb 21 '17 at 23:17
@anang place at least a `PRINT 'Some meesage'` into the `CATCH` block to notice errors if there are any... — Shnugo, Feb 21 '17 at 23:21

Unable to generate tables after using convert for data transfer from XML to SQL Server

1 Answers1

UPDATE