将XML文件读入SQL Server数据库

Question

将XML文件读入SQL Server数据库

3

我正在尝试将一个XML文件读入一个已经存在的数据库表中。问题是XML标签和数据库列虽然拥有相同的数据类型，但它们的名称不同。因此，我想要将XML标签“翻译”成数据库列，以便能够输入到数据库中。

然而，我不确定如何做到这一点。

以下是我迄今为止所做的事情：

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);
    }
}

数据库位于另一台服务器上，我已在app.config文件中包含了此信息。

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

假设XML文件中有标签"Name"，而数据库表列中有列"Firstname"。

XML示例：

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

以下是数据库表的列：

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

这里举个例子，<PersonNr> 标签和数据库列 PersonalIdentityNumber 是相同的。与XML标签不匹配的列应该返回 null。

在将XML数据读入数据库表之前，我认为需要将XML标签翻译为数据库表列。在这种情况下，是"Firstname"。

有人能帮我进行这种"翻译"并将其读入数据库表吗？

- Simon.S

“存储过程”这种方法有什么问题？这是我在这里建议的方法（http://stackoverflow.com/a/36037496/5089204）。 - Shnugo

我需要从文件中读取XML。 - Simon.S

就像我之前告诉你的那样：在C#应用程序中从文件中读取XML，然后将其传递给存储过程。存储过程将会解析XML并将所有数据插入到你指定的列中... - Shnugo

但是您没有提到如何从XML文件中读取，这就是我想知道的。 - Simon.S

嗨，Simon。你可以使用（C#）var xd=new XmlDocument()，然后使用Load()方法。你可以阅读这个。有几种方法可以做到这一点。 - Shnugo

2个回答

0

这是我以前的做法。虽然不是解决方案，但你可以参考一下。

EXEC sp_xml_preparedocument @XML_OUT OUTPUT, @XML_DATA;

这里，@XML_DATA 是您传递的 XML 数据。@XML_OUT 只是一个 INT 类型。

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

但是这些name1，name2等需要与XML文件中的相同。因此，我使用INSERT INTO SELECT查询将此数据插入到指定的表中。例如，

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

- Shanid

1

OPENXML 已经过时了。您应该使用 XML 方法，如.query()、.nodes() 和 .value() ... - Shnugo

1

完全同意@Shnugo的观点。还请注意，在某些情况下，OPENXML会导致内存泄漏（例如，如果您忘记运行sp_xml_removedocument）。 - Devart

这是认真的吗，因为我忘记了 sp_xml_removedocument？ - Shanid

@Noobie，如果您不断运行sp_xml_preparedocument函数。 - Devart

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Devart · Accepted Answer

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)