azure - 数据工厂: Multiple collection reference for XML copy to SQL

Question

我正在尝试设置一个数据工厂管道，它将 XML 数据提取到 Azure SQL 数据库。 XML 遵循以下结构:

<schools>
    <school>
        <students>
            <student></student>
            <student></student>
            <student></student>
        </students>
    </school>
    <school>
        <students>
            <student></student>
            <student></student>
            <student></student>
        </students>
    </school>
<schools>

我已经在 SQL 中设置了多个表来接受此数据。简而言之，有一个学校表将容纳所有学校，还有一个学生表将接受所有学生。

我在数据工厂中设置了复制任务，并且必须将“集合引用”设置为 <school>为了让它迭代学校。如果我不这样做，它只会加载第一所学校并忽略其余学校。

这对于在学校加载来说效果很好。问题在于下一个复制任务，该任务查看相同的 XML 并尝试将所有学校的所有学生复制到学生表中。

如果我将集合引用设置为 <school>它只会复制每个学校的第一个学生，而忽略其余的学生。如果我将集合引用设置为 <student>它将复制第一所学校的所有学生，但忽略其余的学校和学生。

我想迭代所有学校和学生，以便加载所有学校的所有学生，但我没有看到任何简单的方法来做到这一点。有没有办法为学校和学生设置多个集合引用？

Answer 1

XML 从哪里来？我会更多地采用 ELT 方法，将 XML 放入临时表中，然后使用 Azure SQL 数据库的内置 XML 功能，例如 nodes 和 value 方法。一个简化的例子:

在 ADF 中使用 Web Activity 和 Stored Proc 事件的类似 JSON 模式:

用于导入 XML 的示例 SQL:

DROP TABLE IF EXISTS dbo.yourXMLStagingTable
DROP TABLE IF EXISTS dbo.student
DROP TABLE IF EXISTS dbo.school
DROP TABLE IF EXISTS #tmp
GO

CREATE TABLE dbo.yourXMLStagingTable (
    rowId       INT IDENTITY PRIMARY KEY,
    yourXML     XML NOT NULL,
    dateAdded   DATETIME NOT NULL DEFAULT GETDATE(),
    addedBy     VARCHAR(50) NOT NULL DEFAULT SUSER_NAME()
    )
GO


CREATE TABLE dbo.school (
    schoolId    INT IDENTITY PRIMARY KEY,
    schoolName  VARCHAR(50) UNIQUE NOT NULL,
    )
GO


CREATE TABLE dbo.student (
    studentId   INT IDENTITY(1000,1) PRIMARY KEY,
    schoolId    INT FOREIGN KEY REFERENCES dbo.school(schoolId),
    studentName VARCHAR(50) UNIQUE NOT NULL,
    )
GO


-- Use Data Factory to insert the data into a staging table
-- This is just to generate sample data
INSERT INTO dbo.yourXMLStagingTable ( yourXML )
SELECT '<schools>
    <school schoolName = "school1">
        <students>
            <student name = "student11"></student>
            <student name = "student12"></student>
            <student name = "student13"></student>
        </students>
    </school>
    <school schoolName = "school2">
        <students>
            <student name = "student21"></student>
            <student name = "student22"></student>
            <student name = "student23"></student>
        </students>
    </school>
</schools>'
GO

-- Look at the dummy data
SELECT * FROM dbo.yourXMLStagingTable
GO

-- Dump into a staging table
-- Get the schools
SELECT 
    s.rowId,
    schools.c.value('@schoolName', 'VARCHAR(50)') AS schoolName,
    students.c.value('@name', 'VARCHAR(50)') AS studentName
INTO #tmp
FROM dbo.yourXMLStagingTable s
    CROSS APPLY s.yourXML.nodes('schools/school') schools(c)
        CROSS APPLY schools.c.nodes('students/student') students(c)


-- Look at the temp data
SELECT 'temp data' s, * FROM #tmp


-- Insert distinct schools data to schools table
INSERT INTO dbo.school ( schoolName )
SELECT DISTINCT schoolName
FROM #tmp


-- Insert distinct student data to student table, maintaining link to schools table
INSERT INTO dbo.student ( schoolId, studentName )
SELECT DISTINCT s.schoolId, t.studentName
FROM #tmp t
    INNER JOIN dbo.school s ON t.schoolName = s.schoolName
GO


-- End result
SELECT 'end result' s, *
FROM dbo.school school
    INNER JOIN dbo.student student ON school.schoolId = student.schoolId