Geek asks and answers

Import XML to SQL Server Database

I have a very difficult task - to create software that imports XMl files into the MSSQL database. There is one serious problem: each file has a different structure than a datatable. Example:

DATABASE: It has columns such as: ImageURL, Title, Content

XML: Each XML file is different, I will give two examples:

  • <contents>
    <ImageURL>www.123.com/image.png</ImageURL>
    <Title>Some text</title>
    <Content>Content of item</Content>
</contents>
<contents>
    <ImageURL>www.123.com/image.png</ImageURL>
    <Title>Some text</title>
    <Content>Content of item</Content>
</contents>

2.

<item imageURL="url" title="title" content="content">

Is there any open source solution?

* UPDATE *

I forgot to tell you that I will use this code with an ASP.NET application in the following steps:

  • The user selects the URL of the XML document that he wants to import
  • The user will select the tags that he wants to import into the database in the graphical interface.
  • in the code, the rule for importing the current file will be created and saved in the database.

- /, ? , , . ,

+3
3

, , xml-.

SQL Server

1

declare @XML xml

set @XML = 
'<contents>
    <ImageURL>www.123.com/image.png</ImageURL>
    <Title>Some text</Title>
    <Content>Content of item</Content>
</contents>
<contents>
    <ImageURL>www.123.com/image.png</ImageURL>
    <Title>Some text</Title>
    <Content>Content of item</Content>
</contents>'

select 
  N.value('ImageURL[1]', 'varchar(max)') as ImageURL,
  N.value('Title[1]', 'varchar(max)') as Title,
  N.value('Content[1]', 'varchar(max)') as Content
from @XML.nodes('/contents') as T(N)

:

ImageURL               Title      Content
---------------------  ---------  ---------------
www.123.com/image.png  Some text  Content of item
www.123.com/image.png  Some text  Content of item

2:

declare @XML xml
set @XML = '<item imageURL="url" title="title" content="content"></item>'

select 
  N.value('@imageURL', 'varchar(max)') as ImageURL,
  N.value('@title', 'varchar(max)') as Title,
  N.value('@content', 'varchar(max)') as Content
from @XML.nodes('item') as T(N)

:

ImageURL   Title     Content
--------   -----     -------
url        title     content

3.

declare @XML xml

set @XML = 
'<contents>
  <content>
    <someOtherNode>
      <ImageURL>www.FirstURL.com/image.png</ImageURL>
    </someOtherNode>
  </content>
</contents>
<contents>
  <content>
    <someOtherNode>
      <ImageURL>www.SecondURL.com/image.png</ImageURL>
    </someOtherNode>
  </content>
</contents>'

select 
  N.value('ImageURL[1]', 'varchar(max)') as ImageURL
from @XML.nodes('/contents/content/someOtherNode') as T(N)

:

ImageURL
---------------------------
www.FirstURL.com/image.png
www.SecondURL.com/image.png

4.

declare @XML xml

set @XML = 
'<content>
   <imageURL>
     <url>first url</url>
   </imageURL>
   <info>
     <title>title 1</title>
     <text>text 1</text>
   </info>
 </content>
 <content>
   <imageURL>
     <url>second url</url>
   </imageURL>
   <info>
     <title>title 2</title>
     <text>text 2</text>
   </info>
 </content>'

select 
  N.value('imageURL[1]/url[1]', 'varchar(max)') as ImageURL,
  N.value('info[1]/title[1]', 'varchar(max)') as Title,
  N.value('info[1]/text[1]', 'varchar(max)') as Content
from @XML.nodes('/content') as T(N)

:

ImageURL    Title    Content
---------   -------  -------
first url   title 1  text 1
second url  title 2  text 2
+3

?

#, , - .

:

  • Windows WPF-,
    opendialog /s .
    • , , ,
    • Linq

;

public class ContentItem 
{
    public string ImageUrl [get;set;}
    public string Title {get;set;}
    public string Content {get;set;}
}
  • .
+1

You can convert (for example, using xsl) all other formats to standard xml (for example, you decide that the standard scheme is similar in 1 example). For example, you just need to decide which conversion to use for the non-standard xml file.

0
source

More articles:


All Articles