SQL XML 导入:XQuery [value()]: ")";预料之中 [英] SQL XML Import: XQuery [value()]: ")" was expected
问题描述
我正在尝试将数据从 XML 数据插入到 SQL 表中.XML 文件是从 Microsoft Excel 创建的,它提供了以下标题:
I am trying to insert data into a table in SQL from XML data. The XML file was created from Microsoft Excel, which gives it 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">
我正在使用这个查询来解析它:
I am using this query to parse it:
;WITH XMLNAMESPACES ('urn:schemas-microsoft-com:office:spreadsheet' as ss)
select X.value('(ss:Row/ss:Cell/ss:@Data)','varchar(max)')
from @allUsers.nodes('Workbook/Worksheet/Table') as T(X)
它解析了大约半秒,然后给了我这个错误:
which parses for about a half a second and then gives me this error:
XQuery [value()]: ")" 是预期的.
XQuery [value()]: ")" was expected.
被解析的 XML 中的数据包含电话号码,其中一些包含 (
和 )
例如:
The data within the XML being parsed contain phone numbers, some of which contain (
and )
e.g.:
<Row ss:AutoFitHeight="0" ss:Height="30">
<Cell ss:StyleID="s22"/>
<Cell ss:StyleID="s24"><Data ss:Type="String">JohnSmith</Data></Cell>
<Cell ss:StyleID="s24"><Data ss:Type="String">JohnSmith</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>
但我不认为 中的左括号会导致问题.
but I don't think that an open parentheses within the would cause a problem.
我的问题是,之前有没有其他人遇到过这个错误,因为我似乎无法通过在线搜索找到任何帮助?
My question is, has anybody else encountered this error before, since I can't seem to find any help through an online search?
编辑我想在这种情况下我可能走错了方向.我在这里发布了一个新问题:将具有相同标签的 XML 值分隔为不同的行 SQL Server
EDIT I think I may have been going in the wrong direction in this case. I have posted a new question here: Separating XML values with the same tags into different rows SQL Server
推荐答案
您提出了非常相似的问题.我从两者中获取信息并构建了这个工作示例.请注意必须声明为DEFAULT"的 xmlns-namespace:
You asked very similar questions. I took the information from both and built this working example. Be aware of the xmlns-namespace which must be declared as "DEFAULT":
简化了您的 XML,但这个想法应该没问题...
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)
这篇关于SQL XML 导入:XQuery [value()]: ")";预料之中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!