Sql Server 对xml类型的操作
declare @XML XML
SET @XML='<root>
<OLDVALUE>
<H_Action id="1130">030</H_Action>
<D_Action>030</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber>
</OLDVALUE>
<NEWVALUE>
<H_Action>040</H_Action>
<D_Action>040</D_Action>
<OrderCompany>00220</OrderCompany>
<OrderNumber>10004035</OrderNumber>
<OrderType>SO</OrderType>
<LineNumber>10.100</LineNumber>
<LineType>CS</LineType>
<LoadNumber>8811</LoadNumber>
</NEWVALUE>
</root>'
select c.value('H_Action[1]','varchar(20)') as H_Action,
c.value('D_Action[1]','varchar(20)') as D_Action,
c.value('OrderCompany[1]','varchar(20)') as OrderCompany,
c.value('OrderNumber[1]','varchar(20)') as OrderNumber,
c.value('OrderType[1]','varchar(20)') as OrderType,
c.value('LineNumber[1]','varchar(20)') as LineNumber
from @XML.nodes('//OLDVALUE') as T(C)
select c.value('H_Action[1]','varchar(20)') as H_Action,
c.value('D_Action[1]','varchar(20)') as D_Action,
c.value('OrderCompany[1]','varchar(20)') as OrderCompany,
c.value('OrderNumber[1]','varchar(20)') as OrderNumber,
c.value('OrderType[1]','varchar(20)') as OrderType,
c.value('LineNumber[1]','varchar(20)') as LineNumber,
c.value('LineType[1]','varchar(20)') as LineType,
c.value('LoadNumber[1]','varchar(20)') as LoadNumber
from @XML.nodes('//NEWVALUE') as T(C)
CREATE TABLE [dbo].[T_XML](
[ID] [int] NULL,
[DOC] [xml] NULL
)
--query
select doc.query('//NEWVALUE') from t_xml
select doc.query('(root/OLDVALUE/H_Action[@id="1130"])') from t_xml
--
相关文档:
今天试了个XML和JavaBean转换的软件JOX,之前一直有这样的需求,但比较来比较去还是这个比较简单实用。我想除非我有WS的需求,否则象JIBX和APACHE 的WS工具对我来说都是重量级的。
先看看输出结果:
<?xml version="1.0" encoding="ISO-8859-1"?>
<ApproxItem java-class="com.greatwall.csi.np.model.Approx ......
1.route.xml文件内容
<?xml version="1.0" encoding="GBK"?>
<root>
<route id="1111">
<id>111</id>
<name>四川</name>
<path>www.baidu.com/hehe.html</path>
</route>
</root>
2.test.html代码
<html>
<body>
<script> ......
[System.Runtime.Serialization.DataMemberAttribute()]
public Information Archive {
get {
&n ......
//数据绑定
public void DataBind()
{
DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath(@"App_data/dbGuest.xml"));
&nbs ......
DECLARE @HDOC INT --文档句柄
DECLARE @XMLSTRING VARCHAR(200) --XML字符串
SET @xmlString ='<?xml version="1.0"?>
<ROOT>
<USER ID="1" Name="SBQCEL"/>
<USER ID="2" Name="PEACELI"/>
<USER ID="3" Name="SHEEPCHANG"/>
</ROOT>'
--使用系统存储 ......