Импорт XML-файла с несколькими атрибутами
Я пытаюсь импортировать XML-файл, который имеет несколько атрибутов на дочерних узлах, но я не могу заставить его работать.
Это файл XML:
<?xml version="1.0" encoding="UTF-8" ?>
<SelectedPublications>
<Publication>
<ID>
15131585</ID>
<ReceiptLimitDate>
2018-03-05</ReceiptLimitDate>
<OpeningDate/>
<Regions>
<Region RegionID="2" RegionDescription="Belgie"/>
<Region RegionID="36" RegionDescription="Vlaams-Brabant"/>
</Regions>
<Classes>
<Class ClassID="889" ClassDescription="Erkenn. onbekend"/>
</Classes>
<Attachments>
<Attachment FileName="461616_2018-01-26 Minnepoortje BD1 uitbreiding sanitair P1.zip" FileLink="http://www.publicationdownloads.com/Enot/00297726/461616_2018-01-26 Minnepoortje BD1 uitbreiding sanitair P1.zip"/>
<Attachment FileName="2016-11-17 Minnepoortje verslag Vincotte.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2016-11-17 Minnepoortje verslag Vincotte.pdf"/>
<Attachment FileName="2017-01-30 Minnepoortje advies brandweer.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-01-30 Minnepoortje advies brandweer.pdf"/>
<Attachment FileName="2017-02-13 Minnepoortje BD1 sanitair EPB rapport.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-02-13 Minnepoortje BD1 sanitair EPB rapport.pdf"/>
<Attachment FileName="2017-03-15 Minnepoortje advies Infrax.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-03-15 Minnepoortje advies Infrax.pdf"/>
<Attachment FileName="2017-04-10 Minnepoortje vergunning.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-04-10 Minnepoortje vergunning.pdf"/>
<Attachment FileName="2017-04-14 Minnepoortje tek BT - 610X920.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-04-14 Minnepoortje tek BT - 610X920.pdf"/>
<Attachment FileName="2017-04-14 Minnepoortje tek INPL - 610X700.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-04-14 Minnepoortje tek INPL - 610X700.pdf"/>
<Attachment FileName="2017-10-13 Minnepoortje BD1 sanitair P1 offerteformulier.doc" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-10-13 Minnepoortje BD1 sanitair P1 offerteformulier.doc"/>
<Attachment FileName="2017-11-17 Minnepoortje VC Advies_ontwerp.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-11-17 Minnepoortje VC Advies_ontwerp.pdf"/>
<Attachment FileName="2017-11-17 Minnepoortje VC Bijlage VGP.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-11-17 Minnepoortje VC Bijlage VGP.pdf"/>
<Attachment FileName="2017-11-17 Minnepoortje VC VGP.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2017-11-17 Minnepoortje VC VGP.pdf"/>
<Attachment FileName="2018-01-24 Minnepoortje BD1 sanitair P1 adm bepalingen VOMVB.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-24 Minnepoortje BD1 sanitair P1 adm bepalingen VOMVB.pdf"/>
<Attachment FileName="2018-01-24 Minnepoortje BD1 sanitair P1 DM.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-24 Minnepoortje BD1 sanitair P1 DM.pdf"/>
<Attachment FileName="2018-01-24 Minnepoortje BD1 sanitair P1 LB.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-24 Minnepoortje BD1 sanitair P1 LB.pdf"/>
<Attachment FileName="2018-01-24 Minnepoortje BD1 sanitair P1 M aannemers.xlsx" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-24 Minnepoortje BD1 sanitair P1 M aannemers.xlsx"/>
<Attachment FileName="2018-01-24 Minnepoortje BD1 sanitair P1 SM inschrijving.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-24 Minnepoortje BD1 sanitair P1 SM inschrijving.pdf"/>
<Attachment FileName="2018-01-25 Minnepoortje tek NT 610X1200.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-25 Minnepoortje tek NT 610X1200.pdf"/>
<Attachment FileName="2018-01-25 Minnepoortje tek Ramenstaat A3.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/2018-01-25 Minnepoortje tek Ramenstaat A3.pdf"/>
<Attachment FileName="Minnepoortje aankoopvoorwaarden.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje aankoopvoorwaarden.pdf"/>
<Attachment FileName="Minnepoortje asbestinventaris.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje asbestinventaris.pdf"/>
<Attachment FileName="Minnepoortje grondsondering inplanting.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje grondsondering inplanting.pdf"/>
<Attachment FileName="Minnepoortje grondsondering scouts.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje grondsondering scouts.pdf"/>
<Attachment FileName="Minnepoortje kadaster.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje kadaster.pdf"/>
<Attachment FileName="Minnepoortje nota plaatsbezoek.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje nota plaatsbezoek.pdf"/>
<Attachment FileName="Minnepoortje toegangen.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje toegangen.pdf"/>
<Attachment FileName="Minnepoortje werfzone.pdf" FileLink="http://www.publicationdownloads.com/Enot/00297726/Minnepoortje werfzone.pdf"/>
<Attachment FileName="F02 Avis de marché - Officiële tekst (29/01/2018)" FileLink="http://www.publicationdownloads.com/Enot/00297726/297726_0_0_nl-NL.pdf"/>
</Attachments>
<Title>
VRIJE BASISSCHOOL 'T MINNEPOORTJE ASSENT (BEKKEVOORT) - UITBREIDING BASISSCHOOL - BOUWDOSSIER 1: NIEUWBOUW SANITAIRE BLOK VOOR KLEUTERS - perceel 1: algemene bouwwerken</Title>
<Text>
<div lang="NL"><div class="ptxt"><br />Deze mededeling heeft betrekking op de publicatie van een: AANKONDIGING VAN EEN OPDRACHT <br />Uittreksel uit de officiële tekst.<br /><br />
<div class="ppar">AFDELING I. AANBESTEDENDE DIENST</div>
<br />I.1. Naam en adressen<br /><br />Officiële benaming : LABO architecten bvba<br />Postadres : Egide Alenusstraat 12 , BE - 3290 Diest<br />Contactpersoon: architect Marc Bex<br />Tel: +32 13315651<br />E-mail: marc.bex@laboarchitecten.be<br />Internetadres(sen): www.laboarchitecten.be https://enot.publicprocurement.be/enot-war/preViewNotice.do?noticeId=297726<br /><br />I.3. Communicatie<br /><br />Officiële benaming : LABO architecten bvba<br />Postadres : Egide Alenusstraat 12 ,BE -3290 Diest<br />Contactpersoon: 3290<br />Tel: +32 13315651<br />E-mail: marc.bex@laboarchitecten.be<br />Internetadres(sen): www.laboarchitecten.be www.laboarchitecten.be<br /><br />
<div class="ppar">AFDELING II. VOORWERP</div>
<br />II.1. Omvang van de aanbesteding<br /><br />II.1.1. Benaming<br /><b>VRIJE BASISSCHOOL 'T MINNEPOORTJE ASSENT (BEKKEVOORT) - UITBREIDING BASISSCHOOL - BOUWDOSSIER 1: NIEUWBOUW SANITAIRE BLOK VOOR KLEUTERS - perceel 1: algemene bouwwerken </b><br />Referentienummer: LABO architecten bvba-16.452-BD1-P1-F02_0<br />II.1.2. Voornaamste CPV-code : 45000000<br />II.1.3. Type opdracht : werken<br />II.1.4. Korte beschrijving<br />VRIJE BASISSCHOOL 'T MINNEPOORTJE ASSENT (BEKKEVOORT) - UITBREIDING BASISSCHOOL - BOUWDOSSIER 1: NIEUWBOUW SANITAIRE BLOK VOOR KLEUTERS - perceel 1: algemene bouwwerken<br />II.1.6. Inlichtingen over percelen<br />Verdeling in percelen: NEE<br /><br />II.2. Beschrijving<br /><br />II.2.3. Plaats van uitvoering<br />NUTS-code(s): BE242<br />Voornaamste plaats van uitvoering: Assent Bekkevoort<br />II.2.4. Beschrijving<br />VRIJE BASISSCHOOL 'T MINNEPOORTJE ASSENT (BEKKEVOORT) - UITBREIDING BASISSCHOOL - BOUWDOSSIER 1: NIEUWBOUW SANITAIRE BLOK VOOR KLEUTERS - perceel 1: algemene bouwwerken ruwbouw, winddicht, binnenafwerking, buitenaanleg, . geen technieken (= perceel2) info: marc.bex@laboarchitecten.be <br />II.2.5. Gunningscriteria<br />De prijs is niet het enige gunningscriterium en alle criteria worden enkel vermeld in de aanbestedingsdocumenten.<br />II.2.7. Looptijd<br />12 maand(en)<br />Verlenging mogelijk: NEE<br /><br /><br />
<div class="ppar">AFDELING III. JURIDISCHE, ECONOMISCHE, FINANCIELE EN TECHNISCHE INLICHTINGEN</div>
<br />III.1. Voorwaarden voor deelneming<br /><br />III.1.1 Geschikeid om de beroepsactiviteit uit te oefenen, waaronder de vereisten in verband met de inschrijving in het beroeps- of handelsregister<br />Lijst en beknopte beschrijving van de voorwaarden: <br />zie bestek<br /><br /><br />
<div class="ppar">AFDELING IV. PROCEDURE</div>
<br />IV.1.1 Proceduretype<br />Mededingingsprocedure met onderhandeling<br />Versnelde procedure: JA<br />Motivering<br /><br />IV.1.8 Inlichtingen over de Overeenkomst inzake overheidsopdrachten (GPA)<br />De opdracht valt onder de GPA: NEE<br /><br />IV.2 ADMINISTRATIEVE INLICHTINGEN:<br />IV.2.2 Termijn voor de ontvangst van blijken van belangstelling 2018-03-05 14:00<br />IV.2.4 Talen die mogen worden gebruikt bij het indienen van inschrijvingen of aanvragen tot deelneming : NL<br /><br /><br />
<div class="ppar">AFDELING VI. AANVULLENDE INLICHTINGEN</div>
<br /><br />VI.3. Nadere inlichtingen<br />- De aanbestedingsdocumenten kunnen kosteloos digitaal opgevraagd worden bij de ontwerper (marc.bex@laboarchitecten.be). - Plaatsbezoek school kan na telefonische afspraak metdirecteur Tom Vanderbiesen: 013 31 20 88 of 0495 21 00 63- Er is ook nog een BOUWDOSSIER2 "uitbreiding basisschool dmv optoppen op verdieping", zie aparte aankondiging<br /><br />VI.4 Beroepsprocedures<br /><br />VI.4.1 Beroepsinstantie<br />bevoegde rechtbanken , BE nvt<br /><br />VI.5 Datum van verzending van deze aankondiging<br />2018-01-29<br /></div>
</div></Text>
</Publication>
</SelectedPublications>
Это запрос, который я использую, который отлично работает.
DECLARE @XmlFile XML
SELECT @XmlFile = BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\aanbest.xml', SINGLE_BLOB) x;
INSERT INTO Aanbest(ID, ReceiptLimitDate)
SELECT
ID = resource.value('(ID)[1]', 'varchar(50)'),
ReceiptLimitDate = resource.value('(ReceiptLimitDate)[1]', 'varchar(50)')
FROM
@XmlFile.nodes('//SelectedPublications/Publication') AS XTbl1(resource)
WHERE
NOT EXISTS (SELECT a.id FROM Aanbest a
WHERE resource.value('(ID)[1]', 'varchar(50)') = a.id)
Вопрос теперь в том, как я могу также импортировать ссылки вложения? Каждый XML-файл может иметь различное количество ссылок, не более 30.
2 ответа
Вы нашли свое собственное решение - отлично!
Просто несколько подсказок:
<ID>(и большинство других элементов ниже<Publication>) - почему это вообще - начинается с разрыва строки (в вашем случаеCHAR(13)+CHAR(10)) Вы импортируете это какvarchar(50), В будущем у вас могут возникнуть проблемы с решением этой проблемы в виде числового значения (или значения даты).Всякий раз, когда вы чувствуете необходимость нумеровать имя столбца, это сильный указатель на плохой дизайн. Да, вы можете добавить много столбцов в вашу таблицу, как
Attachment1, Attachment2, ..., но это - вообще говоря - неправильный подход.
Чтобы быть справедливым: в вашем случае это может быть идеальным подходом, особенно в тех случаях, когда целью вашего импорта является промежуточная таблица. Но вы могли бы взглянуть на этот подход:
DECLARE @XmlFile XML='YourXmlHere';
- Это макетная таблица, отражающая ваш XML-файл.
--The <Text> является XHTML поэтому я положил это в столбец XML
- и я использую XQuery's substring отрезать ведущие разрывы строк
- Вы можете использовать T-SQL REPLACE() на значение читать тоже
DECLARE @Aanbest TABLE(AanbestID INT,ReceiptLimitDate DATE,Title NVARCHAR(500),[Text] XML);
INSERT INTO @Aanbest(AanbestID,ReceiptLimitDate,Title,[Text])
SELECT @XmlFile.value(N'substring((/SelectedPublications/Publication/ID/text())[1],2)',N'int')
,@XmlFile.value(N'substring((/SelectedPublications/Publication/ReceiptLimitDate/text())[1],2)',N'date')
,@XmlFile.value(N'substring((/SelectedPublications/Publication/Title/text())[1],2)',N'nvarchar(500)')
,@XmlFile.value(N'substring((/SelectedPublications/Publication/Text/text())[1],2)',N'nvarchar(max)');
- Регионы 1:n поэтому я импортирую это в связанную таблицу, к которой вы можете присоединиться AanbestID
--Я использую .nodes() получить все регионы как производную таблицу
DECLARE @Regions TABLE(RegionID INT, AanbestID INT, RegionDescription NVARCHAR(100));
INSERT INTO @Regions(RegionID,AanbestID,RegionDescription)
SELECT A.r.value(N'@RegionID',N'int')
,@XmlFile.value(N'substring((/SelectedPublications/Publication/ID/text())[1],2)',N'int')
,A.r.value(N'@RegionDescription',N'nvarchar(100)')
FROM @XmlFile.nodes(N'/SelectedPublications/Publication/Regions/Region') AS A(r);
- То же самое с приложениями
- Вот я использую ROW_NUMBER() создать номер на ходу (позиция в XML)
DECLARE @Attachments TABLE(AttachmentID INT, AanbestID INT, [FileName] NVARCHAR(500));
INSERT INTO @Attachments(AttachmentID,AanbestID,[FileName])
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
,@XmlFile.value(N'substring((/SelectedPublications/Publication/ID/text())[1],2)',N'int')
,A.a.value(N'@FileName',N'nvarchar(500)')
FROM @XmlFile.nodes(N'/SelectedPublications/Publication/Attachments/Attachment') AS A(a);
- Теперь проверьте вывод:
SELECT * FROM @Aanbest;
SELECT * FROM @Regions;
SELECT * FROM @Attachments;
- Или в присоединенном запросе
SELECT *
FROM @Aanbest AS a
INNER JOIN @Attachments AS at ON a.AanbestID=at.AanbestID
Я нашел решение, добавив достаточное количество столбцов вложений в таблицу SQL (например, attachment1, attachment2 и т. Д.), А затем используя @childnode для заполнения столбца, см. Ниже.
DECLARE @XmlFile XML
SELECT @XmlFile = BulkColumn
FROM OPENROWSET(BULK 'C:\Temp\GeoDynamics\Downloads\aanbest.xml', SINGLE_BLOB) x;
INSERT INTO Aanbest(ID, ReceiptLimitDate, attachment1,attachment2,attachment3)
SELECT
ID = resource.value('(ID)[1]', 'varchar(50)'),
ReceiptLimitDate = resource.value('(ReceiptLimitDate)[1]', 'varchar(50)'),
Attachment1 = resource.value('(@Attachment)[1]', 'varchar(50)'),
Attachment2 = resource.value('(@Attachment)[2]', 'varchar(50)'),
Attachment3 = resource.value('(@Attachment)[3]', 'varchar(50)')
FROM
@XmlFile.nodes('//SelectedPublications/Publication') AS XTbl1(resource)
WHERE
NOT EXISTS (SELECT a.id FROM Aanbest a
WHERE resource.value('(ID)[1]', 'varchar(50)') = a.id)