問題： 我有一個包含 13.5k 資料集的 .xml 檔案，需要將其匯入 SSMS。不幸的是，它包含一種我以前從未見過的風格；首先是列宣告部分，然后是沒有任何特定列名的資料部分。因此，我在捕獲所需欄位方面遇到了問題。最重要的是，.xml 甚至可能已損壞（不正確的層次結構）。
xml:
<?xml version="1.0" encoding="UTF-8"?>
<Root>
<DMSContent format="LOL"/>
<Archive name="Adressdossier" id="52" osguid="43AAEC21AC6C40F1BEDB34D92512ED84"/>
<ObjectType name="Dokument" internal_name="CitizenFileDocument" id="262216" osguid="F287C984EB9E48BEA280BA46C305567C" type="DOCUMENT" modul="MULTIDOC"/>
<Rowset>
<Columns>
<Column name="Salutation" type="TEXT" ostype="X" size="50" otype="FOLDER"/>
<Column name="Name" type="TEXT" ostype="X" size="200" otype="FOLDER"/>
<Column name="FirstName" type="TEXT" ostype="X" size="100" otype="FOLDER"/>
<Column name="StreetNo" type="TEXT" ostype="X" size="100" otype="FOLDER"/>
<Column name="City" type="TEXT" ostype="X" size="150" otype="FOLDER"/>
<Column name="ZIP" type="TEXT" ostype="X" size="50" otype="FOLDER"/>
<Column name="Country" type="TEXT" ostype="X" size="50" otype="FOLDER"/>
<Column name="Birthday" type="DATE" ostype="D" size="50" otype="FOLDER"/>
<Column name="Filename" type="INTEGER" ostype="9" size="100" otype="FOLDER"/>
</Columns>
</Rowset>
<Rows>
<Row id="2538">
<Value>Mrs</Value>
<Value>Doe</Value>
<Value>Jane</Value>
<Value>Main Street 5</Value>
<Value>Ghost Town</Value>
<Value>5315</Value>
<Value>Switzerland</Value>
<Value>12.12.2017</Value>
<Value>jp4_B025DF7DBAFC49879103ECB8AE59C3A2.docx</Value>
</Row>
<Row id="2579">
<Value>Mr</Value>
<Value>Ding</Value>
<Value>Chavez</Value>
<Value>Sun Boulevard 3a</Value>
<Value>Alien City</Value>
<Value>4586</Value>
<Value>Germany</Value>
<Value>01.01.1980</Value>
<Value>jp4_DCA9345C93E84F1697668E6ACDC596C9.docx</Value>
</Row>
<Row id="2580">
<Value>Mr</Value>
<Value>Dale</Value>
<Value>Dick</Value>
<Value>Beach Avenue 13</Value>
<Value>Zombie Village</Value>
<Value>9513</Value>
<Value>Italy</Value>
<Value>09.11.1911</Value>
<Value>jp4_5DDBF2A05BD0421A8C53B0CC4EB64232.doc</Value>
</Row>
</Rows>
</Root>
通常使用的 MS-Sql 代碼片段，當然不適用于這種型別的 .xml 結構：
set ansi_nulls on;
declare @xmlfile xml;
select @xmlfile = bulkcolumn
from openrowset(bulk 'C:\Meta.xml', single_blob) x;
select
id = c.value('@id', 'int'),
Salutation = c.value('(Column[@k="Salutation"]/@v)[1]', 'varchar(60)'),
[Name] = c.value('(Column[@k="name"]/@v)[1]', 'varchar(100)'),
Birthday = c.value('(Column[@k="Birthday"]/@v)[1]', 'date'),
[Filename] = c.value('(Column[@k="Filename"]/@v)[1]', 'varchar(100)')
into #Meta --
from @xmlfile.nodes('/root/rows') as T(c);
set ansi_nulls off;
預先感謝您的任何幫助！
uj5u.com熱心網友回復：
SQL Server 不支持fn::position()或preceding-sibling::語法。但是您可以使用 hack<<來獲取每個節點的位置。
所以我們計算每個Column節點的位置，然后將這些值推送到Value查找中
SELECT
id = x2.Row.value('@id', 'int'),
Salutation = x2.Row.value('(Value[sql:column("ColIndex.Salutation")]/text())[1]', 'varchar(60)'),
[Name] = x2.Row.value('(Value[sql:column("ColIndex.Name" )]/text())[1]', 'varchar(100)'),
Birthday = x2.Row.value('(Value[sql:column("ColIndex.Birthday" )]/text())[1]', 'date'),
[Filename] = x2.Row.value('(Value[sql:column("ColIndex.Filename" )]/text())[1]', 'varchar(100)')
FROM @xml.nodes('/Root/Rowset/Columns') x1(Col)
CROSS APPLY (
SELECT
Salutation = x1.Col.value('let $c:= Column[@name="Salutation"][1] return count(Column[. << $c]) 1', 'int'),
[Name] = x1.Col.value('let $c:= Column[@name="Name"] [1] return count(Column[. << $c]) 1', 'int'),
Birthday = x1.Col.value('let $c:= Column[@name="Birthday"] [1] return count(Column[. << $c]) 1', 'int'),
[Filename] = x1.Col.value('let $c:= Column[@name="Filename"] [1] return count(Column[. << $c]) 1', 'int')
) ColIndex
CROSS APPLY @xml.nodes('/Root/Rows/Row') x2(Row);
資料庫<>小提琴
uj5u.com熱心網友回復：
如果要保留當前匯入檔案的方法，可以進行以下更改：
set ansi_nulls on;
declare @xmlfile xml;
select @xmlfile = bulkcolumn
from openrowset(bulk 'C:\Meta.xml', single_blob) x;
select
id = c.value('@id', 'int'),
Salutation = c.value('(Value[count(/Root/Rowset/Columns/Column[@name="Salutation"]/preceding-sibling::*) 1]/text())[1]', 'varchar(60)'),
[Name] = c.value('(Value[count(/Root/Rowset/Columns/Column[@name="Name"]/preceding-sibling::*) 1]/text())[1]', 'varchar(100)'),
Birthday = c.value('(Value[count(/Root/Rowset/Columns/Column[@name="Birthday"]/preceding-sibling::*) 1]/text())[1]', 'date'),
[Filename] = c.value('(Value[count(/Root/Rowset/Columns/Column[@name="Filename"]/preceding-sibling::*) 1]/text())[1]', 'varchar(100)')
into #Meta --
from @xmlfile.nodes('/Root/Rows/Row') as T(c);
set ansi_nulls off;
這<Value>通過查找<Column>給定名稱的 并確定它前面有多少列來找到正確的位置。不漂亮，但有效。
如果這是一次性的和/或您確定列順序，您當然可以直接訪問這些值。
Birthday = c.value('(Value[8]/text())[1]', 'varchar(60)'),
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/340092.html
標籤：sql-server
上一篇：基于具有相同結構的相似表更新表