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>

You could certainly import that data from the XML into a staging table in SQL Server - one way or another. From there on, you can then validate, enrich, "distribute" that data.... that XML import statement might be a bit messy and huge - but I'm pretty sure you can get this to work. Question is: how does that .map file look like? Lots of data/rows?? Or lots of hierarchical levels in the XML?? — marc_s
– marc_s, Commented Feb 13, 2012 at 6:26
@marc_s. I have edited my question with part of .MAP file. It may have lots of data/rows and. At some places it will have 2-3 hierarchical levels and as I said it contains data which if somehow inserted then it will affect 28 tables.. — Microsoft Developer
– Microsoft Developer, Commented Feb 13, 2012 at 6:37

marc_s · Accepted Answer · 2012-02-13 07:16:47Z

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)

Collectives™ on Stack Overflow

Import XML data in SQL server 2008

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related