将 Excel 行保存在 SQL Server 列(XML 数据类型)中。如何更新特定单元格值?
我将一行 Excel 数据(即 Excel 工作表中的几个单元格)保存到 SQL Server 表中的数据类型为 XML 的列中。
我需要使用存储在同一表的另一列中的新值来更新保存在 XML 列中的数据中特定单元格中的值。 我该怎么做?
我什至无法使用 SQL Server 2005 的 T-SQL 中的 XMLColumnName.Query() 方法选择单元格属性。
粘贴示例表,此处包含示例行,以便您可以进行实验,如果您能够弄清楚这个!谢谢你!!
-湿婆神
-- reference article for XML data manipulation
-- http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx#sql2k5xml_topic3
-- create test table for xml data
CREATE TABLE testdocs (pk INT PRIMARY KEY, xCol XML not null)
-- insert 1 row of test data
insert into testdocs values(1,'<?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 ss:Name="TestSheet">
<Table ss:ExpandedColumnCount="509" ss:ExpandedRowCount="1" ss:StyleID="s191">
<Column ss:StyleID="s1" ss:AutoFitWidth="0" ss:Width="55" />
<Column ss:StyleID="s2" ss:AutoFitWidth="0" ss:Width="55" />
<Row>
<Cell>
<Data ss:Type="String">TestValue1</Data>
</Cell>
<Cell>
<Data ss:Type="String">TestValue2</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>')
-- select records, and inspect the XML in SSMS
select * from testdocs
-- want to replace / update "TestValue2" in the XML for the 1s rows, to "New TestValue2"
-- location in XML hierarchy is as follows
/Workbook/Worksheet/Table/Row/Cell/Data
-- how do i do that ?
I have a row of excel data (i.e. several cells from an excel sheet) saved into a SQL Server table, in a column that is of datatype XML.
I need to update the value, in a particular cell, in this data saved in the XML column, with a new value that is stored in another column in the same table.
How do I do that?
I am not able to even select the cell propery using the XMLColumnName.Query() method in T-SQL of SQL Server 2005.
Pasting a sample table, with sample rows here, so you can experiment and let me know if you were able to figure this out! Thank you!!
-Shiva
-- reference article for XML data manipulation
-- http://msdn.microsoft.com/en-us/library/ms345117(v=sql.90).aspx#sql2k5xml_topic3
-- create test table for xml data
CREATE TABLE testdocs (pk INT PRIMARY KEY, xCol XML not null)
-- insert 1 row of test data
insert into testdocs values(1,'<?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 ss:Name="TestSheet">
<Table ss:ExpandedColumnCount="509" ss:ExpandedRowCount="1" ss:StyleID="s191">
<Column ss:StyleID="s1" ss:AutoFitWidth="0" ss:Width="55" />
<Column ss:StyleID="s2" ss:AutoFitWidth="0" ss:Width="55" />
<Row>
<Cell>
<Data ss:Type="String">TestValue1</Data>
</Cell>
<Cell>
<Data ss:Type="String">TestValue2</Data>
</Cell>
</Row>
</Table>
</Worksheet>
</Workbook>')
-- select records, and inspect the XML in SSMS
select * from testdocs
-- want to replace / update "TestValue2" in the XML for the 1s rows, to "New TestValue2"
-- location in XML hierarchy is as follows
/Workbook/Worksheet/Table/Row/Cell/Data
-- how do i do that ?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
您需要正确获取 XML 命名空间:
更新:
You need to get the XML namespaces properly:
Update: