從包含 XML 列的表中選擇

我收到了一個 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 文件完全陌生，嘗試閱讀不同的文章，但仍然無法弄清楚為什麼我的選擇查詢中沒有顯示任何數據。任何幫助表示讚賞。

由於您的 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。

引用自：https://dba.stackexchange.com/questions/130740