Import XML data in SQL server 2008

Question

I have .MAP file which is created from SAS XML mapper. As the name suggest, the file is derived from XML file. Now I want to insert data from this file in to SQL server 2008 tables. The .MAP file contains data for almost 28 tables. Is there any way to import such a huge data?

This is the sample of .MAP file. The file is too large to share so I am just adding a part of the file to provide some basic idea but can not share actual file.

<?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

Given your input sample, you could "shred" that XML into relational data (rows and columns) with something like this:

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)

This will give you an output something like:

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

Extending that approach, you should be able to fully parse the XML and put it into a intermediate, relational format, which you can then use to go on from there (and put the data where it belongs, in the end)