Read XML file into SQL Server database

sql-server xml tsql bulk-load

15,042

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)

15,042

Author by

Simon.S

Updated on June 05, 2022

Comments

Simon.S almost 2 years

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.

Shnugo about 8 years

OPENXML is outdated. You should use the XML methods like .query(),.nodes() and .value() ...
Devart about 8 years

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)
Shanid about 8 years

Is this serious, because I did forget sp_xml_removedocument ?
Shnugo about 8 years

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 stackoverflow.com/q/36016353/5089204) the OP wants to read the XML with a C# application. Therefore I suggested to pass this through...
Devart about 8 years

@Noobie, yes if you constantly run sp_xml_preparedocument
Devart about 8 years

@Shnugo my bad... Missed this :(