Read XML file into SQL Server database

Question

I'm trying to read an XML file into a database table that already exists.

The problem is that the XML tags and the database columns don't have the same name although they have the same datatype. Therefore I'd like to "translate" the XML tags into the database columns so that the the input to the database becomes possible.

I'm not sure how to do that however.

Here is what I've done so far.

static void writeToDatabase()
{
    XmlDocument doc= new XmlDocument();

    try {
        // Reading the xml
        doc.Load("C:\\Temp\navetout.xml");

        DataTable dt = new DataTable();

        // Code here to read the xml into an already existing database table?
    } 
    catch (Exception e) 
    {
        Console.WriteLine(e.Message);
    }
}

The database is located on another server, I've included this in the app.config

<connectionStrings>
    <add name="CS"
         connectionString="Data Source=tsrv2062;Initial Catalog=BUMS;Integrated Security=True"/>
 </connectionStrings>

Let's say for an example that the XML file has the tags "Name" while the database table column has the column "Firstname".

XML example:

<?xml version="1.0" encoding="utf-8"?>
<ArrayOfFolkbokforingspostTYPE xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <FolkbokforingspostTYPE>
    <Sekretessmarkering xsi:nil="true" />
    <Personpost>
      <PersonId>
        <PersonNr>7527245452542</PersonNr>
      </PersonId>
      <HanvisningsPersonNr xsi:nil="true" />
      <Namn>
        <Tilltalsnamnsmarkering>20</Tilltalsnamnsmarkering>
        <Fornamn>skjdgnsdng</Fornamn>
        <Mellannamn xsi:nil="true" />
        <Efternamn>sdsdgsdgs</Efternamn>
        <Aviseringsnamn xsi:nil="true" />
      </Namn>
      <Folkbokforing>
        <Folkbokforingsdatum>20060512</Folkbokforingsdatum>
        <LanKod>56</LanKod>
        <KommunKod>77</KommunKod>
        <ForsamlingKod xsi:nil="true" />
        <Fastighetsbeteckning>PÅLNGE 6:38</Fastighetsbeteckning>
        <FiktivtNr>0</FiktivtNr>
      </Folkbokforing>
      <Adresser>
        <Folkbokforingsadress>
          <CareOf xsi:nil="true" />
          <Utdelningsadress1 xsi:nil="true" />
          <Utdelningsadress2>sgdsdgsdgs</Utdelningsadress2>
          <PostNr>78965</PostNr>
          <Postort>PÅLÄNG</Postort>
        </Folkbokforingsadress>
        <Riksnycklar>
          <FastighetsId>46464545</FastighetsId>
          <AdressplatsId>764846846</AdressplatsId>
          <LagenhetsId>45465654645</LagenhetsId>
        </Riksnycklar>
      </Adresser>
      <Fodelse>
        <HemortSverige>
          <FodelselanKod>00</FodelselanKod>
          <Fodelseforsamling>NEDERKALIX</Fodelseforsamling>
        </HemortSverige>
      </Fodelse>
      <Medborgarskap>
        <MedborgarskapslandKod>SE</MedborgarskapslandKod>
        <Medborgarskapsdatum>0</Medborgarskapsdatum>
      </Medborgarskap>
    </Personpost>
  </FolkbokforingspostTYPE>
</ArrayOfFolkbokforingspostTYPE>

These are the columns of the database table:

PersonalIdentityNumber
ProtectedIdentity
ReferedCivicRegistrationNumber
UnregistrationReason
UnregistrationDate
MessageComputerComputer
GivenNameNumber
FirstName
MiddleName
LastName
NotifyName
NationalRegistrationDate
NationalRegistrationCountyCode
NationalRegistrationMunicipalityCode
NationalRegistrationCoAddress
NationalRegistrationDistributionAddress1
NationalRegistrationDistributionAddress2
NationalRegistrationPostCode
NationalRegistrationCity
NationalRegistrationNotifyDistributionAddress
NationalRegistrationNotifyPostCode
NationalRegistrationNotifyCity
ForeignDistrubtionAddress1
ForeignDistrubtionAddress2
ForeignDistrubtionAddress3
ForeignDistrubtionCountry
ForeignDate
BirthCountyCode
BirthParish
ForeignBirthCity
CitizenshipCode
CitizenshipDate
Email
Telephone
Mobiletelephone
Gender
NotNewsPaper
Note
StatusCode
NationalRegistrationCode
RegistrationDate
LastUpdatedFromNavet
TemporaryDistrubtionAddress1
TemporaryDistrubtionAddress2
TemporaryDistrubtionAddress3
TemporaryDistrubtionCountry
Password
VisibilityLevel
LastChangedBy
LastChangedDate
SeamanIdentity
Category

Here for an example, the <PersonNr> tagg and the databse column PersonalIdentityNumber are the same. The column that doesn't match with the XML-tags are supposed to returning null.

Before reading the the XML data into the database table, I suppose the XML-tags has to be translated into the Database table column. In this case "Firstname".

Can anyone help me out with this "translation" and the reading into the database table.

What was wrong with the approach "Stored Procedure" [I suggested you here](http://stackoverflow.com/a/36037496/5089204)? — Shnugo, Mar 17 '16 at 13:20
As I told you there: You read the XML from the file within your C# application and pass it through to the stored procedure. The SP will shred the XML and insert all data into the columns you specify... — Shnugo, Mar 17 '16 at 13:28
But you didn't mention how the reading from the XML-file happens, that's what I'm wondering about. — Simon.S, Mar 17 '16 at 13:38
Hi Simon. You might just do a (C#) `var xd=new XmlDocument()` and then use the `Load()` method. [You can read this](http://stackoverflow.com/q/9105009/5089204). There are several methods how you can do this. — Shnugo, Mar 17 '16 at 13:51

score 4 · Accepted Answer · answered Mar 17 '16 at 13:59

4

DECLARE @xml XML
SELECT @xml = BulkColumn
FROM OPENROWSET(BULK 'D:\sample.xml', SINGLE_BLOB) x

SELECT
    t.c.value('(PersonId/PersonNr/text())[1]', 'VARCHAR(100)'),
    t.c.value('(Namn/Tilltalsnamnsmarkering/text())[1]', 'INT')
FROM @xml.nodes('*:ArrayOfFolkbokforingspostTYPE/*:FolkbokforingspostTYPE/*:Personpost') t(c)

answered Mar 17 '16 at 13:59

Devart

119,203
23
166
186

1

The problem with this approach might be with file system rights, if the SQL Server is not running locally. As far as I understood (and there is a long lasting identical question already http://stackoverflow.com/q/36016353/5089204) the OP wants to read the XML with a C# application. Therefore I suggested to pass this through... – Shnugo Mar 17 '16 at 14:13
@Shnugo my bad... Missed this :( – Devart Mar 17 '16 at 14:18

score 0 · Answer 2 · answered Mar 17 '16 at 13:33

0

This is how I used to do. This is not a solutionm but you may refer this.

EXEC sp_xml_preparedocument @XML_OUT OUTPUT, @XML_DATA;

Here, @XML_DATA is the XML data you pass. @XML_OUT is just an INT type.

SELECT * INTO #TEMP
        FROM OPENXML(@XML_OUT,'DATA/INNER_TAG', 1)
        WITH
        (
            a VARCHAR(500),
            b VARCHAR(500),
            c INT,
            d VARCHAR(20)
        )

But these name1, name2 etc need to be the same as in XML file. So I use INSERT INTO SELECT Query to insert this data into specified table. Like,

INSERT INTO OriginalTable
        (
            name1,
            name2,
            name3,
            name4
        )
        SELECT
            a
            b,
            c,
            d
        FROM #TEMP

answered Mar 17 '16 at 13:33

Shanid

557
12
30

1

`OPENXML` is outdated. You should use the XML methods like `.query()`,`.nodes()` and `.value()` ... – Shnugo Mar 17 '16 at 13:47
1

Fully agree with @Shnugo. Also please note that in some cases `OPENXML` lead to memory leaks (for example, if your forgot run `sp_xml_removedocument`) – Devart Mar 17 '16 at 14:09
Is this serious, because I did forget `sp_xml_removedocument` ? – Shanid Mar 17 '16 at 14:12
@Noobie, yes if you constantly run `sp_xml_preparedocument` – Devart Mar 17 '16 at 14:16

Read XML file into SQL Server database

2 Answers2