Robert Morel Robert Morel - 4 months ago 21
SQL Question

Insert into Select only inserts one row

I am attempting to convert an XML file to a MS SQL Server Table but it only inserts the first row. Can anyone explain this please?

I am using insert select to import the data. I have tried everything but the code is so stripped down and I can't see how to fix this.

CREATE DATABASE lei_1
GO

USE lei_1
GO

CREATE TABLE lei_1_table
(
Id INT IDENTITY PRIMARY KEY,
XMLData XML,
LoadedDateTime DATETIME
)

CREATE TABLE recordsx
(
LegalName VARCHAR(100),
la_Line1 [varchar](100),
la_Line2 [varchar](100),
la_City [varchar](100),
la_Region [varchar](100),
la_Country [varchar](100),
la_PostalCode [varchar](100)
);

INSERT INTO lei_1_table(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE()
FROM OPENROWSET(BULK 'C:\lei_example2.xml', SINGLE_BLOB) AS x;

SELECT * FROM lei_1_table

DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


SELECT @XML = XMLData FROM lei_1_table


EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<lei:LEIData xmlns:lei="http://www.leiroc.org/data/schema/leidata/2014" />'

SELECT LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode
FROM OPENXML(@hDoc, 'lei:LEIData')
WITH
(
LegalName [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalName',
la_Line1 [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Line1',
la_Line2 [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Line2',
la_City [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:City',
la_Region [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Region',
la_Country [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Country',
la_PostalCode [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:PostalCode'
)

insert into recordsx(LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode)
SELECT LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode FROM OPENXML(@hDoc, 'lei:LEIData')
WITH
(
LegalName [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalName',
la_Line1 [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Line1',
la_Line2 [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Line2',
la_City [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:City',
la_Region [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Region',
la_Country [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:Country',
la_PostalCode [varchar](100) 'lei:LEIRecords/lei:LEIRecord/lei:Entity/lei:LegalAddress/lei:PostalCode'
)

SELECT * FROM recordsx


EXEC sp_xml_removedocument @hDoc
GO


Here's my XML:

<lei:LEIData xmlns:lei="http://www.leiroc.org/data/schema/leidata/2014">
<lei:LEIRecords>
<lei:LEIRecord>
<lei:LEI>48510000JZ17NWGUA510</lei:LEI>
<lei:Entity>
<lei:LegalName>KDD - Centralna klirin</lei:LegalName>
<lei:LegalAddress>
<lei:Line1>Tivolska cesta 48</lei:Line1>
<lei:City>Ljubljana</lei:City>
<lei:Country>SI</lei:Country>
<lei:PostalCode>1000</lei:PostalCode>
</lei:LegalAddress>
</lei:Entity>
</lei:LEIRecord>
<lei:LEIRecord>
<lei:LEI>485100004VOFFO18DD84</lei:LEI>
<lei:Entity>
<lei:LegalName>NLB VITljana</lei:LegalName>
<lei:LegalAddress>
<lei:Line1>Trg republike 3</lei:Line1>
<lei:City>Ljubljana</lei:City>
<lei:Country>SI</lei:Country>
<lei:PostalCode>1000</lei:PostalCode>
</lei:LegalAddress>
</lei:Entity>
</lei:LEIRecord>
</lei:LEIRecords>
</lei:LEIData>

Answer

The issue is that you select xpath too shallow. Following query returns 2 lines from you xml.

EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML, '<lei:LEIData xmlns:lei="http://www.leiroc.org/data/schema/leidata/2014" />'

SELECT LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode                      
FROM OPENXML(@hDoc, 'lei:LEIData/lei:LEIRecords/lei:LEIRecord')
WITH                          --^ here
(
    LegalName [varchar](100) 'lei:Entity/lei:LegalName',
    la_Line1 [varchar](100) 'lei:Entity/lei:LegalAddress/lei:Line1',
    la_Line2 [varchar](100) 'lei:Entity/lei:LegalAddress/lei:Line2',
    la_City [varchar](100) 'lei:Entity/lei:LegalAddress/lei:City',
    la_Region [varchar](100) 'lei:Entity/lei:LegalAddress/lei:Region',
    la_Country [varchar](100) 'lei:Entity/lei:LegalAddress/lei:Country',
    la_PostalCode [varchar](100) 'lei:Entity/lei:LegalAddress/lei:PostalCode'
)

--insert into recordsx(LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode)
SELECT LegalName, la_Line1, la_Line2, la_City, la_Region, la_Country, la_PostalCode 
FROM OPENXML(@hDoc, 'lei:LEIData/lei:LEIRecords/lei:LEIRecord/lei:Entity')
WITH                          --^ here
(
    LegalName [varchar](100) 'lei:LegalName',
    la_Line1 [varchar](100) 'lei:LegalAddress/lei:Line1',
    la_Line2 [varchar](100) 'lei:LegalAddress/lei:Line2',
    la_City [varchar](100) 'lei:LegalAddress/lei:City',
    la_Region [varchar](100) 'lei:LegalAddress/lei:Region',
    la_Country [varchar](100) 'lei:LegalAddress/lei:Country',
    la_PostalCode [varchar](100) 'lei:LegalAddress/lei:PostalCode'
)

--SELECT * FROM recordsx


EXEC sp_xml_removedocument @hDoc

Additionally, if you declare @xml as XML type then you can use native xml methods like .nodes() and so on.
To say truth in my experience these native methods are slower than openxml you use.

Example of native xml methods.

--from OP
DECLARE @XML AS XML
SELECT @XML = XMLData FROM lei_1_table

--@xml is already xml type. So use it.
--honor xmlns:lei="..." but not necessarily under the same name
;with xmlnamespaces('http://www.leiroc.org/data/schema/leidata/2014' as x)
--insert tbl goes here
--note [1]. value() requires a singleton
select t.v.value('../x:LEI[1]','varchar(100)') LEI -- note ../ for parent node
   ,t.v.value('x:LegalName[1]','varchar(100)') LegalName
   ,t.v.value('(x:LegalAddress/x:Line1)[1]','varchar(100)') Line1
   ,t.v.value('(x:LegalAddress/x:Line2)[1]','varchar(100)') Line2
   ,t.v.value('(x:LegalAddress/x:City)[1]','varchar(100)') City
   ,t.v.value('(x:LegalAddress/x:Region)[1]','varchar(100)') Region
   ,t.v.value('(x:LegalAddress/x:Country)[1]','varchar(100)') Country
   ,t.v.value('(x:LegalAddress/x:PostalCode)[1]','varchar(100)') PostalCode
from @xml.nodes('x:LEIData/x:LEIRecords/x:LEIRecord/x:Entity') t(v)