主页 / dba / 问题 / 130740
Accepted
Sky
Asked: 2016-02-29 16:56:34 +0800 CST

从包含 XML 列的表中选择

  • 772

我收到了一个 Excel 文件（2010 版），根据要求，我被要求将其保存为 XML 文件，然后导入 SQL 数据，以便在正常的 SELECT 语句中查询。

下面是我遵循的步骤列表，但现在我对数据有一个 SELECT，没有返回任何记录。我不确定这是否是由于我从 Excel 文件中获取 XML 文件所做的转换，还是我的OPENXML查询有问题。

  1. 为了XML从文件中获取.xlsx文件，我在 EXCEL 2010 中打开了该文件并将其保存在“XML 电子表格 2003 (*.XML)”中。

  2. 我使用以下代码将 XML 文件导入到表中：

    INSERT INTO XMLwithOpenXML(XMLData, LoadedDateTime)
SELECT CONVERT(XML, BulkColumn) AS BulkColumn, GETDATE() 
FROM OPENROWSET(BULK 'C:\DEV\TestXML.xml', SINGLE_BLOB) AS x;


SELECT * FROM XMLwithOpenXML

在此处输入图像描述 1. XML 数据现在已存储在表中，因此我使用下面的代码在 SELECT 语句中读取它以访问各个列：

    DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)


 SELECT @XML = XMLData FROM XMLwithOpenXML

    EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML


    SELECT SNo,Salutation,PatientNRIC,FirstName,LastName,Gender,Race
    FROM OPENXML(@hDoc, 'ROOT/SNo/Salutation/PatientNRIC/FirstName/LastName/Gender/Race')
    WITH 
    (
        SNo [nvarchar](50) '@SNo',
        Salutation [nvarchar](100) '@Salutation',
        PatientNRIC [nvarchar](255) '@PatientNRIC',
        FirstName [nvarchar](255) '@FirstName',
        LastName [nvarchar](255) '@LastName',
        Gender [nvarchar](255) '@Gender',
        Race [nvarchar](255) '@Race'
    )

然后

EXEC sp_xml_removedocument @hDoc
    GO

但是我得到的只是列的名称，没有返回实际数据：

在此处输入图像描述

我对 OPENXML 评论和使用 XML 文件完全陌生，尝试阅读不同的帖子，但仍然无法弄清楚为什么我的选择查询中没有显示任何数据。任何帮助表示赞赏。

sql-server import

1 个回答

  1. Best Answer

    由于您的 XML 保存在一个表中，您可以使用XML 数据类型的方法（例如 .nodes、.value、.query 等）。您可能会遇到一点困难的是 XML 中的名称空间。从这里开始了解更多信息：了解 XML 命名空间。

    以下是我针对类似于您的电子表格所做的一些示例查询。按照@MikaelEriksson 的建议尝试它们并通过楼梯工作，看看它们是否开始有意义。

    IF OBJECT_ID('tempdb.#tmp') IS NOT NULL
    DROP TABLE #tmp
GO


-- 1) Pull the data "as is" unpivoted, tag it with row/cell numbers
-- and optionally pivot it
;WITH XMLNAMESPACES ( 
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss   
    ), cte AS (
SELECT
    t.rawXMLId,
    ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) rowNumber,
    r.c.query('.') rowXML
FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        CROSS APPLY w.c.nodes('Table/Row') r(c)
)
SELECT
    c.rawXMLId,
    c.rowNumber,
    ROW_NUMBER() OVER ( PARTITION BY rowNUmber ORDER BY rowNumber ) cellNumber,
    d.c.value('.', 'VARCHAR(50)') AS cellData
INTO #tmp
FROM cte c
    CROSS APPLY c.rowXML.nodes('Row/Cell/Data') d(c)
GO


SELECT 'unpivoted' s, *
FROM #tmp

SELECT 
    'pivoted' s,
    rawXMLId, rowNumber, 
    [1] AS Sno,
    [2] AS Salutation,
    [3] AS PatientNRIC,
    [4] AS FirstName,
    [5] AS LastName,
    [6] AS Gender,
    [7] AS Race
FROM #tmp
PIVOT ( MAX(cellData) For cellNumber In ( [1], [2], [3], [4], [5], [6], [7] )  ) upvt
GO



-- 2) Use your knowledge about the data to specify the columns manually
;WITH XMLNAMESPACES ( 
    DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet',
    'urn:schemas-microsoft-com:office:spreadsheet' AS ss   
    )
SELECT 
    t.rawXMLId,
    d.c.value('(Cell/Data/text())[1]', 'VARCHAR(50)') AS Sno,
    d.c.value('(Cell/Data/text())[2]', 'VARCHAR(50)') AS Salutation,
    d.c.value('(Cell/Data/text())[3]', 'VARCHAR(50)') AS PatientNRIC,
    d.c.value('(Cell/Data/text())[4]', 'VARCHAR(50)') AS FirstName,
    d.c.value('(Cell/Data/text())[5]', 'VARCHAR(50)') AS LastName,
    d.c.value('(Cell/Data/text())[6]', 'VARCHAR(50)') AS Gender,
    d.c.value('(Cell/Data/text())[7]', 'VARCHAR(50)') AS Race

FROM XMLwithOpenXML t
    CROSS APPLY XMLData.nodes('Workbook/Worksheet[@ss:Name = "Sheet1"]') w(c)
        --CROSS APPLY w.c.nodes('Table/Row') d(c)                   <-- include row header
        CROSS APPLY w.c.nodes('Table/Row[position() > 1]') d(c)     -- exclude row header

GO

    我的结果：

    试验结果

    只是关于 的注释OPENXML，它对于较大的 XML 片段可能很好，但我倾向于尽可能避免它，因为它是众所周知的内存问题。由于命名空间，您的特定示例无法正常工作，并且您指定了错误的路径（根等）。如果您真的想使用OPENXML. 回帖，但我建议不要在此示例中使用它，因为您的数据保存在一个表中 -OPENXML一次只能使用一次 XML。

    • 2

相关问题