如何将数据从XML导入SQL Server？

时间：2021-03-07 00:54:44

Can anyone help me work towards fixing my solution to import my XML data into a SQL Server Table? I have done my research, but this task has been very hard for me to accomplish. Everything I have found has worked for simple data, but my name and value are separate in the "website_details" section. This is why my script has not been working. I have around 200 XML files, some with more than a thousand records each to import, so I cannot change their structure. The data I am working with is considered confidential, so I have changed the value names and values to enable me to post this.

任何人都可以帮我修复我的解决方案,将我的XML数据导入SQL Server表吗?我做了我的研究,但这项任务对我来说很难完成。我发现的所有内容都适用于简单数据,但我的名称和价值在“website_details”部分中是分开的。这就是我的脚本没有工作的原因。我有大约200个XML文件,其中一些每个都有超过一千条记录要导入,所以我无法改变它们的结构。我正在使用的数据被认为是保密的,因此我更改了值名称和值,以便我发布此信息。

The first insert command for the websites table works perfectly and imports all of my data. The issue I am having is with the second insert command. The @xmlData.nodes definition is where I think the issue is. In the "website_details" section I am having a hard time defining the structure because the name and value are separate unlike the other information.

网站表的第一个插入命令工作正常,并导入我的所有数据。我遇到的问题是第二个插入命令。 @ xmlData.nodes定义是我认为问题所在。在“website_details”部分中,我很难定义结构,因为名称和值与其他信息不同。

Just to give an overview of my database at the moment it consists of two tables. They are websites and website_details. The Web_ID column is included in both tables and is the foreign key that connects website_details to websites. I also have a view that I am using to combine my data called website_view.

只是为了概述我的数据库,它包含两个表。它们是网站和website_details。 Web_ID列包含在两个表中,并且是将website_details连接到网站的外键。我还有一个观点,我用来组合我的数据,名为website_view。

I have been working on this for a little over couple of weeks, and have finally concluded that I need a little help to get this going.

我已经花了几个星期的时间研究这个问题,并最终得出结论,我需要一些帮助来实现这一目标。

Here is sample data pulled from my XML file:

以下是从我的XML文件中提取的示例数据:

<WEBSITES>
    <WEBSITE>
        <WEBSITE_ID>sta001</WEBSITE_ID>
        <WEBSITE_ALTERNATE_ID/>
        <WEBSITE_VERSION>4</WEBSITE_VERSION>
        <TYPE>DYNAMIC</TYPE>
        <NAME>TEST WEBSITE</NAME>
        <WEBSITE_DETAILS>
            <WEBSITE_DETAIL>
                <NAME>COST</NAME>
                <VALUE>500</VALUE>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
            <WEBSITE_DETAIL>
                <NAME>LANGUAGE</NAME>
                <VALUE>EN</VALUE>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
            <WEBSITE_DETAIL>
                <NAME>DATABASE</NAME>
                <VALUE/>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
        </WEBSITE_DETAILS>
    </WEBSITE>
    <WEBSITE>
        <Website_ID>mmn023</WEBSITE_ID>
        <WEBSITE_ALTERNATE_ID/>
        <WEBSITE_VERSION>3</WEBSITE_VERSION>
        <TYPE>DYNAMIC</TYPE>
        <NAME>TEST WEBSITE 2</NAME>
        <WEBSITE_DETAILS>
            <WEBSITE_DETAIL>
                <NAME>COST</NAME>
                <VALUE>750</VALUE>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
            <WEBSITE_DETAIL>
                <NAME>LANGUAGE</NAME>
                <VALUE>RU</VALUE>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
            <WEBSITE_DETAIL>
                <NAME>DATABASE</NAME>
                <VALUE>TRUE</VALUE>
                <INHERITED>false</INHERITED>
            </WEBSITE_DETAIL>
        </WEBSITE_DETAILS>
    </WEBSITE>
</WEBSITES>

Here is the stored procedure I am using:

这是我正在使用的存储过程:

USE [websitesDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SP_website_import] (
 @xmlData XML ,
 @retValue varchar(100) OUTPUT
)

AS
BEGIN
SET @retValue='Failed';



INSERT INTO  [websites](
[Web_ID],
[Web_Version],
[Web_Type],
[Web_Name]
)

SELECT
[Table].[Column].value('WEBSITE_ID [1]', 'nvarchar(100)'),
[Table].[Column].value('WEBSITE_VERSION [1]', 'nvarchar(100)'),
[Table].[Column].value('TYPE [1]', 'nvarchar(100)'),
[Table].[Column].value('NAME [1]', 'nvarchar(100)')

 FROM @xmlData.nodes('/ WEBSITES / WEBSITE') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
  SET @retValue='SUCCESS';


INSERT INTO  [website_details](
[Web_ID],
[cost],
[language],
[database]
)

SELECT
[Table].[Column].value('WEBSITE_ID [1]', 'nvarchar(100)'),
[Table].[Column].value('COST [1]', 'nvarchar(100)'),
[Table].[Column].value('LANGUAGE [1]', 'nvarchar(100)'),
[Table].[Column].value('DATABASE [1]', 'nvarchar(100)')

 FROM @xmlData.nodes('/ WEBSITES / WEBSITE / WEBSITE_DETAILS') as [Table]([Column])
IF(@@ROWCOUNT > 0 )
  SET @retValue='SUCCESS'

;

1 个解决方案

#1

0  

When working in XQuery on SQL Server i like to got directly to the node i need data from, when working with data islands, then traverse back using "../". For example getting the WEBSITE_ID from the WEBSITE_DETAIL island you can use the following.

在SQL Server上使用XQuery时,我喜欢直接访问节点,我需要数据来处理数据岛,然后使用“../”进行遍历。例如,从WEBSITE_DETAIL岛获取WEBSITE_ID,您可以使用以下内容。

SELECT
[Table].[Column].value('../../WEBSITE_ID[1]', 'nvarchar(100)'),
[Table].[Column].value('NAME[1]', 'nvarchar(100)'),
[Table].[Column].value('VALUE[1]', 'nvarchar(100)'),
[Table].[Column].value('INHERITED[1]', 'nvarchar(100)')
FROM @xmlData.nodes('/WEBSITES/WEBSITE/WEBSITE_DETAILS/WEBSITE_DETAIL') as [Table]([Column])

#1

0  

When working in XQuery on SQL Server i like to got directly to the node i need data from, when working with data islands, then traverse back using "../". For example getting the WEBSITE_ID from the WEBSITE_DETAIL island you can use the following.

在SQL Server上使用XQuery时,我喜欢直接访问节点,我需要数据来处理数据岛,然后使用“../”进行遍历。例如,从WEBSITE_DETAIL岛获取WEBSITE_ID,您可以使用以下内容。

SELECT
[Table].[Column].value('../../WEBSITE_ID[1]', 'nvarchar(100)'),
[Table].[Column].value('NAME[1]', 'nvarchar(100)'),
[Table].[Column].value('VALUE[1]', 'nvarchar(100)'),
[Table].[Column].value('INHERITED[1]', 'nvarchar(100)')
FROM @xmlData.nodes('/WEBSITES/WEBSITE/WEBSITE_DETAILS/WEBSITE_DETAIL') as [Table]([Column])
标签：

相关文章