問(wèn)題描述
我有一個(gè)要解析的 XML 文件.XML 是使用
I have an XML File which I am trying to parse. The XML was created through Excel using
另存為 XML
因?yàn)?XML 文件是從 Microsoft Excel 創(chuàng)建的，所以它有這個(gè)標(biāo)題:
Because the XML file was created from Microsoft Excel, it has this header:
<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
我試圖提取的數(shù)據(jù)是這樣設(shè)置的:
The data I am trying to extract is set up in blocks like this:
<Row ss:AutoFitHeight="0" ss:Height="30">
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s24"><Data ss:Type="String">Jane Doe</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">JaneDoe</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">XYZ</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">(555) 555-5555</Data></Cell>
<Cell ss:StyleID="s22"/>
</Row>
現(xiàn)在，我的查詢?nèi)缦滤?
Right now, my query looks like this:
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss)
select * from (
select X.value('local-name(.)[1]','varchar(max)') as Name,
X.value('.[1]','varchar(max)') as Value
from @allUsers.nodes('//*') as T(X)
) a
where Name = 'Data'
并給我這些結(jié)果:
Name Value
---- -----------
Data Jane Doe
Data JaneDoe
Data XYZ
Data (555)555-5555
我想做的是將它分成 4 行，所以我有類似的東西:
What I would like to do is separate this into 4 rows, so I have something like:
Name UserName Address Phone
----- ---------- --------- ----------
Jane Doe JaneDoe XYZ (555)-555-5555
我嘗試選擇一列作為
X.value('.[2]','varchar(max)') as UserName
但我只是得到了所有的 NULL 值.
but I just get all NULL values for that.
有沒(méi)有辦法做到這一點(diǎn)?
Is there any way to do this?
XML 文件的一般結(jié)構(gòu)如下:
The general structure of the XML file looks like:
<Workbook>
<DocumentProperties>
</DocumentProperties>
<ExcelWorkbook>
</ExcelWorkbook>
<Styles>
<Style>
</Style>
</Styles>
<Worksheet>
<Table>
<Column.../>
<Column.../>
<Column.../>
<Row>
<Cell.../>
<Cell><Data>...</Data></Cell>
<Cell><Data>...</Data></Cell>
<Cell><Data>...</Data></Cell>
<Cell><Data>...</Data></Cell>
<Cell.../>
</Row>
...
</Table>
</Worksheet>
我想要獲取的信息在 ...</Data> 字段
and the information I am trying to get is in the <Data>...</Data> field
編輯
從我表述這個(gè)問(wèn)題的方式來(lái)看，標(biāo)題名稱似乎已經(jīng)被編入，但它們實(shí)際上被讀取為
From the way I phrased the question, it would seem like the header names are already programmed in, but they are actually read as rows in <Cell><Data><Data/></Cell>. I am also not sure what purpose the part serves
這是
<Table ss:ExpandedColumnCount="6" ss:ExpandedRowCount="2685" x:FullColumns="1"
x:FullRows="1">
<Column ss:AutoFitWidth="0" ss:Width="26.25"/>
<Column ss:AutoFitWidth="0" ss:Width="117" ss:Span="3"/>
<Column ss:Index="6" ss:AutoFitWidth="0" ss:Width="29.25"/>
<Row ss:AutoFitHeight="0" ss:Height="60"> --Contains the header names
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s23"><Data ss:Type="String">Name</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">UserName</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">Address</Data></Cell>
<Cell ss:StyleID="s23"><Data ss:Type="String">Telephone Number</Data></Cell>
<Cell ss:StyleID="s22"/>
</Row>
<Row ss:AutoFitHeight="0" ss:Height="30"> --First record I would like to extract
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s24"><Data ss:Type="String">John Smith</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">JSmith</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">ABC</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">(999) 999-9999</Data></Cell>
<Cell ss:StyleID="s22"/>
</Row>
推薦答案
同一用戶提出了兩個(gè)非常相似的問(wèn)題.OP 決定刪除一個(gè)并在此處合并，并要求我將我的答案從那里復(fù)制到此線程.
There were two very similar question by the same user. The OP decided to delete one and combine this here and asked me to copy my answer from there to this thread.
注意必須聲明為DEFAULT"的 xmlns-namespace:
Be aware of the xmlns-namespace which must be declared as "DEFAULT":
簡(jiǎn)化了您的 XML，但這個(gè)想法應(yīng)該沒(méi)問(wèn)題...
Simplified your XML, but the idea should be OK...
DECLARE @allUsers XML=
'<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:html="http://www.w3.org/TR/REC-html40">
<Worksheet>
<Table>
<Row ss:AutoFitHeight="0" ss:Height="30">
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s24"><Data ss:Type="String">Jane Doe</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">JaneDoe</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">XYZ</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">(555) 555-5555</Data></Cell>
<Cell ss:StyleID="s22"/>
</Row>
</Table>
</Worksheet>
</Workbook>';
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss
,DEFAULT 'urn:schemas-microsoft-com:office:spreadsheet')
SELECT T.X.value('Cell[1]/Data[1]','varchar(max)') AS DontKnow1
,T.X.value('Cell[2]/Data[1]','varchar(max)') AS Name
,T.X.value('Cell[3]/Data[1]','varchar(max)') AS UserName
,T.X.value('Cell[4]/Data[1]','varchar(max)') AS DontKnow2
,T.X.value('Cell[5]/Data[1]','varchar(max)') AS Telephone
,T.X.value('Cell[6]/Data[1]','varchar(max)') AS DontKnow3
FROM @allUsers.nodes('/Workbook/Worksheet/Table/Row') as T(X)
這篇關(guān)于將具有相同標(biāo)記的 XML 值分成不同的行 SQL Server的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！