Sql-Server-2008
SQL Server 中的 XQuery 將 XML 列數據轉換為關係數據表
我之前問過一個關於我收到的錯誤的問題。您並不真的需要它來理解這個問題，但它在這裡供參考：
XML/SQL Server 2008 錯誤：XQuery…無法隱式原子化或將“fn:data()”應用於復雜的內容元素
以前的 xml 有點複雜，可能會從轉換中受益，所以我應用了一個 XSLT 模板來獲得下面的結構，並稍微更改了標籤，使其更易於理解。為了可維護性，我還重組了要導入的表。我將轉換後的 XML 文件導入到 SQL Server 表中
xTable，使用 columnxData，就像這樣（只有一行，但我想你可以導入多個 1 並將它們全部與 David Browne 的答案合併）：ID xData 1 <MyXMLFile><Sample><Location>....xml 的父節點
<Sample>, 最多可以重複 100 萬次，但為了這個說明，我只有 2 個。每個樣本有 22 個子節點，一個<SampleID>節點和 21<Location>個節點（我只顯示了 2 個要保留的節點事情很短）。每個節點有 3 個子節點，一個<LocationName>節點和兩個<Foo>節點，分別指定<Foo1>和<Foo2>。<?xml version="1.0" encoding="UTF-16"?> <MyXMLFile> <!--There CAN BE up to 1 million <Sample> nodes--> <Sample> <!--There ARE EXACTLY 22 child nodes for each <Sample> parent node, one <SampleID> and 21 <Location>--> <SampleID>0000001A</SampleID> <!--There ARE EXACTLY 3 child nodes for each <Location> parent node, on <LocationID> and two <Foo>--> <Location> <LocationName>Jeff</LocationName> <Foo1>10</Foo1> <Foo2>11</Foo2> </Location> <Location> <LocationName>Jenn</LocationName> <Foo1>11</Foo1> <Foo2>12</Foo2> </Location> </Sample> <Sample> <SampleID>0000002A</SampleID> <Location> <LocationName>Greg</LocationName> <Foo1>13</Foo1> <Foo2>14</Foo2> </Location> <Location> <LocationName>Anne</LocationName> <Foo1>14</Foo1> <Foo2>16</Foo2> </Location> </Sample> </MyXMLFile>我想轉換
xData列xTable並將其放入此表（ID 列僅用於說明）：ID SampleID LocationName Foo1 Foo2 1 00000001 Jeff 10 11 2 00000001 Jenn 11 12 … 00000001 … … … 22 00000001 … … … 23 00000002 Greg 13 14 24 00000002 Anne 17 18 … 00000002 … … … 44 00000002 … … …目前，我只是嘗試
SELECT從xData列中獲取xTable，稍後將編輯查詢以插入數據。所以我的第一個查詢，只是為了表明<SampleID>確實被選中：查詢 1
SELECT a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID FROM xTable CROSS APPLY xData.nodes('MyXMLFile/Sample') as a(b)輸出看起來不錯：
ID SampleID 1 00000001 2 00000002所以，我添加到查詢中：
查詢2
SELECT a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID, a.b.query('LocationName').value('.', 'varchar(10)') AS LocationName, a.b.query('Foo1').value('.', 'varchar(6)') AS Foo1, a.b.query('Foo2').value('.', 'varchar(6)') AS Foo2 FROM xTable CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as a(b)對於此輸出，沒有為 選擇任何數據
<SampleID>。這對我來說並不奇怪，因為 xpath 選擇只針對<Location>父節點並返回其子節點<LocationName>，<Foo1>而<Foo2>不是<SampleID>。ID SampleID LocationName Foo1 Foo2 1 Jeff 10 11 2 Jenn 11 12 … … … … 22 … … … 23 Greg 13 14 24 Anne 17 18 … … … … 44 … … …所以我嘗試了這個：
查詢 3
SELECT a.b.query('SampleID').value('.', 'varchar(20)') AS SampleID, c.d.query('LocationName').value('.', 'varchar(10)') AS LocationName, c.d.query('Foo1').value('.', 'varchar(6)') AS Foo1, c.d.query('Foo2').value('.', 'varchar(6)') AS Foo2 FROM xTable CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID') as a(b) CROSS APPLY xData.nodes('MyXMLFile/Sample/SampleID/../Location') as c(d)輸出稍微好一點，但表中的行重複了。應該只有 44 個，但有 88 個：
ID SampleID LocationName Foo1 Foo2 1 00000001 Jeff 10 11 2 00000001 Jenn 11 12 … 00000001 … … … 42 00000001 … … … 43 00000001 … … … 44 00000001 … … … 45 00000002 Greg 13 14 46 00000002 Anne 17 18 … … … … … 88 00000002 … … …然後我想我會嘗試不同的方式。
查詢 4
DECLARE @x xml; SELECT @x = xData FROM xTable SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID, a.b.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName, a.b.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1, a.b.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2 FROM @x.nodes('MyXMLFile/Sample') AS xData(a) CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS a(b)現在，不是空白
SampleID欄位或重複記錄，而是SampleID返回NULL並且數據被重複：ID SampleID LocationName Foo1 Foo2 1 NULL Jeff 10 11 2 NULL Jenn 11 12 … NULL … … … 42 NULL … … … 43 NULL … … … 44 NULL … … … 45 NULL Greg 13 14 46 NULL Anne 17 18 … NULL … … … 88 NULL … … …所以在最後一次嘗試選擇正確的數據時，我嘗試了這個查詢：
查詢 5
DECLARE @x xml; SELECT @x = xData FROM xTable SELECT a.b.value('(SampleID/text())[1]', 'varchar(20)') AS SampleID, c.d.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName, c.d.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1, c.d.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2 FROM @x.nodes('MyXMLFile/Sample') AS xData(a) CROSS APPLY @x.nodes('MyXMLFile/Sample') AS a(b) CROSS APPLY @x.nodes('MyXMLFile/Sample/SampleID/../Location') AS c(d)這裡的結果更令我驚訝，查詢不僅填充了所有欄位，而且使輸出翻了兩番：
ID SampleID LocationName Foo1 Foo2 1 00000001 Jeff 10 11 2 00000001 Jenn 11 12 … 00000001 … … … … 00000001 … … … … 00000001 … … … 44 00000001 … … … 45 00000002 Greg 13 14 46 00000002 Anne 17 18 47 00000002 … … … 48 00000002 … … … … … … … … 176 00000002 … … …我理解我的問題是將兩個不同的 xpath 合併到查詢中，以及我對查詢中派生表的理解和使用。任何幫助，將不勝感激。如何調整這些查詢以獲得我需要的表？
提前致謝。
編輯： 根據大衛布朗的回答，這對我有用：
查詢 6
INSERT INTO MyTable (SampleID, LocationName, Foo1, Foo2) SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleName, Location.n.value('(LocationName/text())[1]', 'varchar(1)') AS LocationName, Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1, Location.n.value('(Foo2/text())[1]', 'varchar(6)') As Foo2 FROM xTable AS x CROSS APPLY x.xData.nodes('/MYXMLFile/Sample') AS Sample(n) CROSS APPLY Sample.n.nodes('Location') AS Location(n)
模式是每個都
cross apply獲取父級的相對位置。嘗試這樣的事情：declare @doc xml =N'<?xml version="1.0" encoding="UTF-16"?> <MyXMLFile> <!--There CAN BE up to 1 million <Sample> nodes--> <Sample> <!--There ARE EXACTLY 22 child nodes for each <Sample> parent node, one <SampleID> and 21 <Location>--> <SampleID>0000001A</SampleID> <!--There ARE EXACTLY 3 child nodes for each <Location> parent node, on <LocationID> and two <Foo>--> <Location> <LocationName>Jeff</LocationName> <Foo1>10</Foo1> <Foo2>11</Foo2> </Location> <Location> <LocationName>Jenn</LocationName> <Foo1>11</Foo1> <Foo2>12</Foo2> </Location> </Sample> <Sample> <SampleID>0000002A</SampleID> <Location> <LocationName>Greg</LocationName> <Foo1>13</Foo1> <Foo2>14</Foo2> </Location> <Location> <LocationName>Anne</LocationName> <Foo1>14</Foo1> <Foo2>16</Foo2> </Location> </Sample> </MyXMLFile>' drop table if exists #xData; with q as ( select 1 ID, @doc xData union all select 1 ID, @doc xData ) select * into #xData from q SELECT Sample.n.value('(SampleID)[1]', 'varchar(20)') AS SampleID, Location.n.value('(LocationName/text())[1]', 'varchar(10)') AS LocationName, Location.n.value('(Foo1/text())[1]', 'varchar(6)') AS Foo1, Location.n.value('(Foo2/text())[1]', 'varchar(6)') AS Foo2 FROM #xData x cross apply x.xData.nodes('/MyXMLFile/Sample') AS Sample(n) cross apply Sample.n.nodes('Location') as Location(n)輸出
SampleID LocationName Foo1 Foo2 -------------------- ------------ ------ ------ 0000001A Jeff 10 11 0000001A Jenn 11 12 0000002A Greg 13 14 0000002A Anne 14 16 0000001A Jeff 10 11 0000001A Jenn 11 12 0000002A Greg 13 14 0000002A Anne 14 16 (8 rows affected)