将具有相同标签的XML值分隔到不同的行中SQL Server

将具有相同标签的XML值分隔到不同的行中SQL Server

同一用户有两个非常相似的问题。OP决定删除一个并将其合并到此处，并要求我将答案从此处复制到该线程。

请注意必须声明为“ DEFAULT”的xmlns-namespace：

简化了您的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">(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 DontKnow3FROM @allUsers.nodes('/Workbook/Worksheet/Table/Row') as T(X)