将具有相同标记的XML值分隔为不同的行SQL Server

Question

我有一个XML文件，我试图解析它。该XML使用

保存为XML

通过Excel中创建因为XML文件是从Microsoft Excel中创建的，它有这个标题：

<?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"> 

我想数据提取物是这样设置的：

<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">[phone]</Data></Cell> 
    <Cell ss:StyleID="s22"/> 
    </Row> 

现在，我的查询是这样的：

;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' 

，并给了我这些结果：

Name Value 
---- ----------- 
Data Jane Doe 
Data JaneDoe 
Data XYZ 
Data [phone] 

我想这样做是分开到这4行，所以我有这样的：

Name  UserName Address Phone 
-----  ---------- --------- ---------- 
Jane Doe JaneDoe  XYZ  (555)-555-5555 

我试过选择一列作为

X.value('.[2]','varchar(max)') as UserName 

但我只是得到了所有NULL值。

有没有办法做到这一点？

的XML文件的一般结构如下：

<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>...</Data>领域

编辑

从我措辞的方式这个问题看起来好像是头文件的名字已经被编入了，但是它们实际上被读作<Cell><Data><Data/></Cell>中的行。我也不能肯定的部分成为什么样的目的

这是<Row>部分的开头：

<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">[phone]</Data></Cell> 
    <Cell ss:StyleID="s22"/> 
    </Row> 

Answer 1

同一个用户有两个非常相似的问题。 OP决定删除一个，并在此合并，并要求我将答案从那里复制到此主题。

注意必须声明为“DEFAULT”的XMLNS命名空间的：

简化您的XML，但这个想法应该是好的...

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">[phone]</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) 

Answer 2

试试这样说：我加了根元素暗示的命名空间，你必须把它拿出来（也是从XPath的），但你可以通过简单的拷贝一个空的查询窗口进行测试，粘贴和执行：

DECLARE @allUsers XML= 
'<root xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"> 
    <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">[phone]</Data></Cell> 
    <Cell ss:StyleID="s22"/> 
    </Row> 
</root>'; 

;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss) 
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('/root/Row') as T(X) 

编辑：你的路会像/Workbook[1]/Worksheet[1]/Table[1]/Row[1]

祝你好运！