代码之家 › 专栏 › 技术社区 › ViewtifulStranger

在同一节点级别导入数据的SQL OPENXML问题

openxml nodes tsql sql-server

ViewtifulStranger · 技术社区 · 8 年前

我有一个XML格式的日志文件,我想为其安排自动导入SQL。我可以使用OPENROWSET和OPENXML部分导入数据,但是我无法导入一些数据,因为它们处于同一节点级别。具体来说,“用户”节点与“存储对象”处于同一级别,“文件柜”节点与“客户端”、“物质”等处于同一级别。

XML示例:

<ActivityLog repositoryId="ZZ-T6KQ1I1B" repositoryName="Training" startDate="2017-07-01" endDate="2017-09-18">
    <activity date="2017-08-08T10:29:02" name="change access list" host="127.0.0.1">
        <user id="JS" name="John Smith" memberType="I" />
        <storageObject docId="4161-1264-9996" name="Hello World" size="0" fileExtension="eml">
            <cabinet name="Materials">QPIRVQK</cabinet>
        </storageObject>
    </activity>
    <activity date="2017-08-08T10:29:03" name="change access list" host="127.0.0.1">
        <user id="JS" name="John Smith" memberType="I" />
        <storageObject docId="4161-1264-9996" name="Screen Shot" size="0" fileExtension="jpg">
            <cabinet name="Materials">QPIRVQK</cabinet>
            <Client>1011</Client>
            <Matter>007</Matter>
            <Author>EMAIL</Author>
            <DocumentType>JPG</DocumentType>
        </storageObject>
    </activity>
</ActivityLog>

我的T-SQL脚本:

DECLARE @x xml

SELECT @x=L
FROM OPENROWSET (BULK 'C:\Log.xml', SINGLE_BLOB) AS Log(L)

DECLARE @hdoc int

EXEC sp_xml_preparedocument @hdoc OUTPUT, @x

SELECT *
FROM OPENXML (@hdoc, 'ActivityLog/activity/storageObject/cabinet', 1)
WITH (
        activitydate datetime '../../@date',
        activityname nvarchar(max) '../../@name',
        host nvarchar(32) '../../@host',
        id nvarchar(32) '../@id',
        username nvarchar(max) '../@username',
            memberType nvarchar(50) '../@memberType',
        docId nvarchar(50) '../@docId',
        filename nvarchar(max) '../@filename',
        size int '../@size',
        fileExtension nvarchar(max) '../@fileExtension',
        cabinetname nvarchar(max) '@cabinetname',
        Client nvarchar(max) '@Client',
        Matter nvarchar(max) '@Matter',
        Author nvarchar(max) '@Author',
        DocumentType nvarchar(max) '@DocumentType'
    )

EXEC sp_xml_removedocument @hdoc

上面的脚本返回除用户标记之外的所有内容:id、username(name)、memberType和Client、Matter、Author和DocumentType。如果有人能就如何导入所有活动提供建议,我们将不胜感激。

1 回复 | 直到 8 年前

Community Mohan Dere 5 年前

我不习惯和 OPENXML ,据我所知,这是从XML文档中检索内容的老派方法。我这样做的方式是使用SQL Server中支持的XML函数。具体来说,XML函数:

nodes

nodes()方法的结果是一个行集,其中包含原始XML实例的逻辑副本。在这些逻辑副本中,每个行实例的上下文节点都设置为使用查询表达式标识的节点之一,以便后续查询可以相对于这些上下文节点进行导航。
value

对XML执行XQuery并返回SQL类型的值

id ( <user> 属性Id)您处于错误的级别,必须再深入一步。例如。 <Client>

DECLARE @x XML;
SET @x=N'<ActivityLog repositoryId="ZZ-T6KQ1I1B" repositoryName="Training" startDate="2017-07-01" endDate="2017-09-18">
    <activity date="2017-08-08T10:29:02" name="change access list" host="127.0.0.1">
        <user id="JS" name="John Smith" memberType="I" />
        <storageObject docId="4161-1264-9996" name="Hello World" size="0" fileExtension="eml">
            <cabinet name="Materials">QPIRVQK</cabinet>
        </storageObject>
    </activity>
    <activity date="2017-08-08T10:29:03" name="change access list" host="127.0.0.1">
        <user id="JS" name="John Smith" memberType="I" />
        <storageObject docId="4161-1264-9996" name="Screen Shot" size="0" fileExtension="jpg">
            <cabinet name="Materials">QPIRVQK</cabinet>
            <Client>1011</Client>
            <Matter>007</Matter>
            <Author>EMAIL</Author>
            <DocumentType>JPG</DocumentType>
        </storageObject>
    </activity>
</ActivityLog>';

SELECT
    activitydate=n.v.value('../../@date','DATETIME'),
    activityname=n.v.value('../../@name','NVARCHAR(MAX)'),
    host=n.v.value('../../@name','NVARCHAR(32)'),
    id=n.v.value('../../user[1]/@id','NVARCHAR(32)'),
    username=n.v.value('../../user[1]/@name','NVARCHAR(MAX)'),
    memberType=n.v.value('../../user[1]/@memberType','NVARCHAR(50)'),
    docId=n.v.value('../@docId','NVARCHAR(50)'),
    filename=n.v.value('../@filename','NVARCHAR(MAX)'),
    size=n.v.value('../@size','INT'),
    fileExtension=n.v.value('../@fileExtension','NVARCHAR(MAX)'),
    cabinetname=n.v.value('@cabinetname','NVARCHAR(MAX)'),
    Client=n.v.value('../Client[1]','NVARCHAR(MAX)'),
    Matter=n.v.value('../Matter[1]','NVARCHAR(MAX)'),
    Author=n.v.value('../Author[1]','NVARCHAR(MAX)'),
    DocumentType=n.v.value('../DocumentType[1]','NVARCHAR(MAX)')
FROM
    @x.nodes('ActivityLog/activity/storageObject/cabinet') AS n(v);