3

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.

Improve this question
5
  • What was wrong with the approach "Stored Procedure" I suggested you here? Commented Mar 17, 2016 at 13:20
  • Well I need to read the XML from a file. Commented Mar 17, 2016 at 13:24
  • 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... Commented Mar 17, 2016 at 13:28
  • But you didn't mention how the reading from the XML-file happens, that's what I'm wondering about. Commented Mar 17, 2016 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. There are several methods how you can do this. Commented Mar 17, 2016 at 13:51

2 Answers 2

Reset to default
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)
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

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...
@Shnugo my bad... Missed this :(
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
Improve this answer

4 Comments

OPENXML is outdated. You should use the XML methods like .query(),.nodes() and .value() ...
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)
Is this serious, because I did forget sp_xml_removedocument ?
@Noobie, yes if you constantly run sp_xml_preparedocument

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.