将所有XML文件导入到SQL Server中

发布于12月13日

如何更正此SQL查询以读取C:\Files\文件夹中存在的所有XML文件，而不仅仅是Test.xml文件？

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);

推荐答案

与其try 完全从SQL执行此操作，而不是使用正确的脚本语言(如PowerShell)将数据推送到SQL中，因为它不是为执行直接文件访问而设计的，并且充满了问题.

您可以使用dbaTools' Invoke-DbaQuery来执行带参数的SQL命令.只需获取所需文件的内容，并将它们作为SQL参数推送到管道中.

$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 = $_ };