如何拆分xml并将值存储到列中 [英] how to split the xml and store the value into a column

问题描述

< updateapproval>< request>< requestid> 54361stdid acw_id =134665156> O427970< role> XYZBC

我在一列中有这样的XML标记。我想捕获acw_id =134665156并将值134665156存储到acw_id列中。



请帮我修改存储过程。

<updateapproval><request><requestid>54361stdid acw_id="134665156">O427970<role>XYZBC
I have a XML tag like this in one column. I want to capture acw_id="134665156" and store the value "134665156" into acw_id column.

Please help me how to modify the store procedure.

推荐答案

declare @T table
(
  Records xml
)
declare @c_xml_data varchar(1000)

set @c_xml_data = (select c_xml from bc_xml_errors where n_id=453048)

insert into @T values(@c_xml_data)
select
       V.X.value('@acw_id', 'nvarchar(10)') as c_acw_id
from @T as T
 cross apply T.Records.nodes('updateapproval/request/approver/stdid') as V(X)

其中@c_xml是基于n_id的动态xml。我们还必须遍历T.Records.nodes中的欲望节点

where @c_xml is my dynamic xml based on the n_id. also we have to traverse till the desire node in T.Records.nodes

尝试

http://blog.sqlauthority.com/2009/02/13/sql-server- simple-example-of-reading-xml-file-using-t-sql / [ ^ ]

http://www.sqlservercentral.com/Forums/Topic1108062-392-1.aspx [ ^ ]

这篇关于如何拆分xml并将值存储到列中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！