Import all XML files into SQL Server

Question

How to correct this SQL query to read all XML files that exists in the C:\Files\ folder and not just the Test.xml file?

CREATE TABLE Projet 
(
    UID int, 
    nom varchar(50), 
    prenom varchar(50), 
    matricule varchar(50), 
    lien varchar(100)
)

INSERT INTO Projet (UID, nom, prenom, matricule, lien)
    SELECT
        page.value('(item[@name="UID(Zonal OCR)"]/@value)[1]', 'INT') AS UID,
        page.value('(item[@name="nom(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS nom,
        page.value('(item[@name="prenom(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS prenom,
        page.value('(item[@name="matricule(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS matricule,
        page.value('(item[@name="lien"]/@value)[1]', 'VARCHAR(100)') AS lien 
    FROM 
        (SELECT 
             CAST(MY_XML AS xml)
         FROM 
             OPENROWSET(BULK 'C:\Files\Test.xml', SINGLE_BLOB) AS T(MY_XML)) AS T(MY_XML)
    CROSS APPLY 
        MY_XML.nodes('root/page') AS MY_XML (page);

Answer 1

Rather than trying to do this completely from SQL, which is not designed to do direct file access and is fraught with issues, instead use a proper scripting language such as Powershell to push the data into SQL.

You can use dbaTools' Invoke-DbaQuery to execute SQL commands with parameters. Simply get the contents of files you want and push them through in the pipeline as SQL parameters.

$sql = @"
INSERT INTO Projet (UID, nom, prenom, matricule, lien)
    SELECT
        page.value('(item[@name="UID(Zonal OCR)"]/@value)[1]', 'INT') AS UID,
        page.value('(item[@name="nom(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS nom,
        page.value('(item[@name="prenom(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS prenom,
        page.value('(item[@name="matricule(Zonal OCR)"]/@value)[1]', 'VARCHAR(50)') AS matricule,
        page.value('(item[@name="lien"]/@value)[1]', 'VARCHAR(100)') AS lien 
    FROM (VALUES(CAST(@xml AS xml)) v1(xml)
    CROSS APPLY v1.xml.nodes('root/page') AS MY_XML (page);
"@;

Get-ChildItem -Path 'C:\Files\' |
  Get-Content -Raw |
  Invoke-DbaQuery -SqlInstance 'YourServer' -Database 'YourDB' -Query $sql -SqlParameter @{ xml = $_ };