将数据库XML列数据移动到新的相关表 [英] Moving database XML column data to new related tables

问题描述

基本上我有两个表， Teachertbl ，主键为 TeacherID ，和 TeacherBlobtbl 和 TeacherID 与 TeacherTbl 相关，以及XML列 TeacherBLOB 。

Basically I have two tables, Teachertbl with primary key of TeacherID, and TeacherBlobtbl with TeacherID relating to TeacherTbl, as well as an XML column TeacherBLOB.

TeacherBLOB XML数据保存教师数据，例如他们的班级和学生。

The TeacherBLOB XML data holds teacher data such as their class and students.

表的架构如下：

TeacherTbl：

TeacherID int PRIMARY KEY    
-- Other columns

TeacherBlobTbl：

TeacherID int FOREIGN KEY REFERENCES TeacherTbl (TeacherID)
TeacherBlob xml

TeacherBlob 如下所示：

<Teacher>
. . .
    <TeacherClass>
        <FormRoom> Room A</FormRoom>
        <TotalStudents> 25 </TotalStudents>
        <Subject> Mathematics </Subject>
            <Student>
                <StudentName> James </StudentName>
                <StudentAge> 15 </StudentAge>
                <StudentAddress> </StudentAddress>
            </Student>
    </TeacherClass>
</Teacher>

基本上我想获得 TeacherClass 和 Student 并将其移动到自己的表中。我想获取 TeacherClass ，并将其数据存储到带有 TeacherTbl 外键的新表中。

Basically I want to get TeacherClass and Student and move them to their own tables. I want to grab TeacherClass and it's data to a new table with a foreign key relating to TeacherTbl.

除此之外，我还要获取与 StudentClass Student c $ c>，并使用与 TeacherClass 相关的外键将其移到自己的表中。

In addition to this I want to grab each Student associated with StudentClass and move it into it's own table with a foreign key relating to TeacherClass.

注意：不想将 Teacher 移到自己的表中，我只关心 TeacherClass 和学生。

Note: I do not want to move Teacher into its own table, I am only concerned about TeacherClass and Student.

它应该类似于以下内容：

It should look something like the following:

TeacherClasstbl：

TeacherClassID int PRIMARY KEY
FormRoom VARCHAR(50)
TotalStudents int
Subject VARCHAR(100)
TeacherID int FOREIGN KEY REFERENCES TeacherTbl(TeacherID)

Studenttbl：

StudentID int PRIMARY KEY
StudentName VARCHAR(50)
StudentAge int
StudentAddress VARCHAR(100)
TeacherClass int FOREIGN KEY REFERENCES TeacherClassTbl(TeacherClassID)

它还值得注意的是，并不是每个XML Teacher 都会有一个关联的 TeacherClass 元素，并且该解决方案需要满足 TeacherBlobTbl 中的新数据。

It's also worth noting that not every XML Teacher will have an associated TeacherClass element and that the solution will need to cater for new data coming in to the TeacherBlobTbl.

请让我知道我可以用来实现的任何工具/技术这个。

Please let me know any tools/technologies I could use to achieve this.

我已经考虑过要按计划运行存储过程，不确定是否有更好的解决方案。

I've thought about making a stored procedure that runs on a schedule, not sure if there is a better solution.

谢谢！

推荐答案

我建议将每个级别读入登台表table并从中实际转移在那里：

I'd suggest to read each level into staging tables table and to the actual transfer from there:

我使用声明的表变量模拟您的测试方案

I use declared table variables to mock-up your test scenario

DECLARE @Teacher TABLE(TeacherID INT IDENTITY,Name VARCHAR(100));
INSERT INTO @Teacher VALUES('Teacher 1'),('Teacher 2');
DECLARE @TeacherBLOB TABLE(TeacherBLOBID INT IDENTITY,TeacherID INT /*FK*/, TeacherBLOB XML);
INSERT INTO @TeacherBLOB VALUES
 (1,'<Teacher>
    <TeacherClass>
        <FormRoom> Room A</FormRoom>
        <TotalStudents> 25 </TotalStudents>
        <Subject> Mathematics </Subject>
            <Student>
                <StudentName> James </StudentName>
                <StudentAge> 15 </StudentAge>
                <StudentAddress> </StudentAddress>
            </Student>
    </TeacherClass>
</Teacher>')
,(1,'<Teacher>
    <TeacherClass>
        <FormRoom> Room B</FormRoom>
        <TotalStudents> 20 </TotalStudents>
        <Subject> Physics </Subject>
            <Student>
                <StudentName> Jane </StudentName>
                <StudentAge> 13 </StudentAge>
                <StudentAddress> Some address </StudentAddress>
            </Student>
            <Student>
                <StudentName> Tim </StudentName>
                <StudentAge> 14 </StudentAge>
                <StudentAddress> Some address </StudentAddress>
            </Student>
    </TeacherClass>
</Teacher>')
,(2,'<Teacher>
    <TeacherClass>
        <FormRoom> Room B</FormRoom>
        <TotalStudents> 20 </TotalStudents>
        <Subject> German </Subject>
            <Student>
                <StudentName> Hugo </StudentName>
                <StudentAge> 14 </StudentAge>
                <StudentAddress> Some address </StudentAddress>
            </Student>
            <Student>
                <StudentName> Max </StudentName>
                <StudentAge> 13 </StudentAge>
                <StudentAddress> Some address </StudentAddress>
            </Student>
    </TeacherClass>
</Teacher>');

-第一个查询读取TeacherClass

--The first query reads the TeacherClass

SELECT t.Name
      ,tc.query('.') AS TeacherClassXML
      ,tc.value('FormRoom[1]','nvarchar(max)') AS FormRoom
      ,tc.value('TotalStudents[1]','int') AS TotalStudents
INTO #TeacherClass
FROM @Teacher AS t
INNER JOIN @TeacherBLOB AS tb ON t.TeacherID=tb.TeacherID
OUTER APPLY tb.TeacherBLOB.nodes('/Teacher/TeacherClass') AS A(tc);

-第二个查询读取主题

SELECT tc.*
      ,s.query('.') AS SubjectXML
      ,tc.TeacherClassXML.value('(TeacherClass/Subject)[1]','nvarchar(max)') AS [Subject]
INTO #Subject
FROM #TeacherClass AS tc
OUTER APPLY tc.TeacherClassXML.nodes('TeacherClass/Subject') AS B(s) 

-此查询读取学生

SELECT tc.*
      ,tc.TeacherClassXML.value('(TeacherClass/Subject)[1]','nvarchar(max)') AS [Subject]
      ,st.query('.') AS StudentXML
      ,st.value('StudentName[1]','nvarchar(max)') AS StudentName
      ,st.value('StudentAge[1]','int') AS StudentAge
      ,st.value('StudentAddress[1]','nvarchar(max)') AS StudentAddress
INTO #Student
FROM #TeacherClass AS tc
OUTER APPLY tc.TeacherClassXML.nodes('TeacherClass/Student') AS B(st) 

-检查内容

SELECT * FROM #TeacherClass;
SELECT * FROM #Subject;
SELECT * FROM #Student;

-清理测试

GO
DROP TABLE #Student;
DROP TABLE #Subject;
DROP TABLE #TeacherClass;

在清理之前，您必须放置代码以填写您的代码真实表。

Before the Clean-Up you have to place your code to fill your real tables.

这篇关于将数据库XML列数据移动到新的相关表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！