xml - 在 SQL Server 2008 中导入 XML 数据

Question

我有从 SAS XML 映射器创建的 .MAP 文件。顾名思义，该文件源自 XML 文件。现在我想将此文件中的数据插入到 SQL Server 2008 表中。 .MAP 文件包含几乎 28 个表的数据。有没有办法导入这么庞大的数据？

这是 .MAP 文件的示例。该文件太大无法共享，所以我只是添加了文件的一部分以提供一些基本概念，但无法共享实际文件。

<?xml version="1.0" encoding="UTF-8"?>
<!-- ############################################################ -->
<!-- 2012-02-10T13:13:14 -->
<!-- SAS XML Libname Engine Map -->
<!-- Generated by XML Mapper, 902000.3.6.20090116170000_v920 -->
<!-- ############################################################ -->
<!-- ###  Validation report                                   ### -->
<!-- ############################################################ -->
<!-- XMLMap validation completed successfully. -->
<!-- ############################################################ -->
<SXLEMAP name="AUTO_GEN" version="1.2">

<!-- ############################################################ -->
<TABLE name="Patients">
    <TABLE-DESCRIPTION>Patients</TABLE-DESCRIPTION>
    <TABLE-PATH syntax="XPath">/Patients</TABLE-PATH>

    <COLUMN name="Patients_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

</TABLE>

<TABLE name="Patient">
    <TABLE-DESCRIPTION>Patient</TABLE-DESCRIPTION>
    <TABLE-PATH syntax="XPath">/Patients/Patient</TABLE-PATH>

    <COLUMN name="Patients_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

    <COLUMN name="Patient_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients/Patient</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

    <COLUMN name="PatientID">
        <PATH syntax="XPath">/Patients/Patient/PatientID</PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

</TABLE>
</SXLEMAP>

Answer 1

给定您的输入示例，您可以使用以下内容将该 XML“粉碎”为关系数据(行和列):

DECLARE @input XML = '<?xml version="1.0" encoding="UTF-8"?>
<SXLEMAP name="AUTO_GEN" version="1.2">
<TABLE name="Patients">
    <TABLE-DESCRIPTION>Patients</TABLE-DESCRIPTION>
    <TABLE-PATH syntax="XPath">/Patients</TABLE-PATH>

    <COLUMN name="Patients_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

</TABLE>

<TABLE name="Patient">
    <TABLE-DESCRIPTION>Patient</TABLE-DESCRIPTION>
    <TABLE-PATH syntax="XPath">/Patients/Patient</TABLE-PATH>

    <COLUMN name="Patients_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

    <COLUMN name="Patient_ORDINAL" ordinal="YES">
        <INCREMENT-PATH beginend="BEGIN" syntax="XPath">/Patients/Patient</INCREMENT-PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

    <COLUMN name="PatientID">
        <PATH syntax="XPath">/Patients/Patient/PatientID</PATH>
        <TYPE>numeric</TYPE>
        <DATATYPE>integer</DATATYPE>
    </COLUMN>

</TABLE>
</SXLEMAP>'


SELECT
    TableName = Map.Tbl.value('@name', 'varchar(50)'),
    TableDescription = Map.Tbl.value('(TABLE-DESCRIPTION)[1]', 'varchar(50)'),
    TablePath = Map.Tbl.value('(TABLE-PATH)[1]', 'varchar(50)'),
    ColumnName = Map2.Col.value('@name', 'varchar(50)'),
    ColumnPath = Map2.Col.value('(PATH)[1]', 'varchar(50)'),
    ColumnIncrementPath = Map2.Col.value('(INCREMENT-PATH)[1]', 'varchar(50)'),
    ColumnType = Map2.Col.value('(TYPE)[1]', 'varchar(50)'),
    ColumnDataType = Map2.Col.value('(DATATYPE)[1]', 'varchar(50)')
FROM
    @input.nodes('/SXLEMAP/TABLE') AS Map(Tbl)
CROSS APPLY 
    Map.Tbl.nodes('COLUMN') AS Map2(Col)

这将为您提供如下输出:

TableName  TableDescription TablePath         ColumnName        ColumnPath           ColumnIncrementPath    ColumnType  ColumnDataType
Patients   Patients         /Patients         Patients_ORDINAL  NULL           /Patients    numeric integer
Patient    Patient          /Patients/Patient Patients_ORDINAL  NULL           /Patients    numeric integer
Patient    Patient          /Patients/Patient Patient_ORDINAL   NULL           /Patients/Patient    numeric integer
Patient    Patient          /Patients/Patient PatientID         /Patients/Patient/PatientID NULL    numeric integer

扩展这种方法，您应该能够完全解析 XML 并将其放入中间的关系格式，然后您可以使用它从那里继续(并将数据放在它所属的地方，最后)