将 'xml' 导入 Sql Server [英] Import 'xml' into Sql Server

问题描述

我有一个结构如下的文件:

I have a file that is structured like so:

<?xml version="1.0" encoding="UTF-8"?>
<EventSchedule>
    <Event Uid="2" Type="Main Event">
        <IsFixed>True</IsFixed>
        <EventKind>MainEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
    <Event>
    ...and so on
    </Event>
</EventSchedule>

我不完全确定它是否是有效的 XML，但是我需要将它导入 SQL Server，但我尝试的一切似乎都不起作用.

I'm not entirely sure if it is valid XML, however I need to import it into SQL Server but everything I try doesn't seem to work.

请谁能用一些示例代码或推荐使用哪种方法来为我指明正确的方向?

Please could anyone point me in the right direction either with some example code or a recommendation on which method to use?

理想情况下，我希望将原始数据放入一个平面表格中，如下所示:

I'd ideally like to get the raw data into a flat table, along the lines of:

Name        | Type    | Duration | EventKind

The Muppets | TV_Show | 00:30:00 | MainEvent

最后，这是来自相当大的文件，我需要定期导入.

Finally this is coming from fairly large files and I will need to import the regularly.

谢谢，pugu

推荐答案

试试这个:

DECLARE @XML XML = '<EventSchedule>
    <Event Uid="2" Type="Main Event">
        <IsFixed>True</IsFixed>
        <EventKind>MainEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
    <Event Uid="3" Type="Secondary Event">
        <IsFixed>True</IsFixed>
        <EventKind>SecondaryEvent</EventKind>
        <Fields>
            <Parameter Name="Type" Value="TV_Show"/>
            <Parameter Name="Name" Value="The Muppets II"/>
            <Parameter Name="Duration" Value="00:30:00"/>
        </Fields>
    </Event>
</EventSchedule>'

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    EventIsFixed =Events.value('(IsFixed)[1]', 'varchar(20)'),
    EventKind =Events.value('(EventKind)[1]', 'varchar(20)')
FROM
 @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

给我一​​个输出:

当然，你可以很容易地做一个

And of course, you can easily do an

INSERT INTO dbo.YourTable(EventUID, EventType, EventIsFixed, EventKind)
   SELECT 
         ......

将该数据插入到关系表中.

to insert that data into a relational table.

更新:假设您在文件中有 XML - 您可以使用此代码将 XML 文件加载到 SQL Server 中的 XML 变量中:

Update: assuming you have your XML in files - you can use this code to load the XML file into an XML variable in SQL Server:

DECLARE @XmlFile XML

SELECT @XmlFile = BulkColumn
FROM  OPENROWSET(BULK 'path-to-your-XML-file', SINGLE_BLOB) x;

然后使用上面的代码片段来解析 XML.

and then use the above code snippet to parse the XML.

更新 #2:如果您也需要参数 - 使用此 XQuery 语句:

Update #2: if you need the parameters, too - use this XQuery statement:

SELECT
    EventUID = Events.value('@Uid', 'int'),
    EventType = Events.value('@Type', 'varchar(20)'),
    EventIsFixed = Events.value('(IsFixed)[1]', 'varchar(20)'),
    EventKind = Events.value('(EventKind)[1]', 'varchar(20)'),
    ParameterType = Events.value('(Fields/Parameter[@Name="Type"]/@Value)[1]', 'varchar(20)'),
    ParameterName = Events.value('(Fields/Parameter[@Name="Name"]/@Value)[1]', 'varchar(20)'),
    ParameterDuration = Events.value('(Fields/Parameter[@Name="Duration"]/@Value)[1]', 'varchar(20)')
FROM
    @XML.nodes('/EventSchedule/Event') AS XTbl(Events)

结果:

这篇关于将 'xml' 导入 Sql Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！