存储过程处理XML问题?
存储过程怎样把XML格式数据插入到临时表?
XMLXML内容如下:
xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly' rs:updatable='true'>
<s:AttributeType name='no' rs:number='1' rs:writeunknown='true' rs:basecatalog='lkhz' rs:basetable='cOrder_d'
rs:basecolumn='no'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='18' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='color' rs:number='2' rs:writeunknown='true' rs:basecatalog='lkhz' rs:basetable='cOrder_d'
rs:basecolumn='color'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='6' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='plate' rs:number='3' rs:writeunknown='true' rs:basecatalog='lkhz' rs:basetable='cOrder_d'
rs:basecolumn='plate'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='4' rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='s1' rs:number='4' rs:nullable='true' rs:writeunknown='true' rs:basecatalog='lkhz' rs:basetable='cOrder_d'
rs:basecolumn='s1'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true'/>
</s:AttributeType>
<s:AttributeType name='ID' rs:number='5' rs:basecatalog='lkhz' rs:basetable='cOrder_d' rs:basecolumn='ID' rs:keycolumn='true'
rs:hidden='true' rs:autoincrement='true'>
<s:datatype dt:type='int' dt:maxLength='4' rs:precision='10' rs:fixedlength='true' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row no='AW20410 ' color='- ' plate='B' ID='8'/>
</rs:data>
</xml> 存储 XML String
[解决办法]
create table #aa(a varchar(8000))
insert into .....
[解决办法]
有什么更简便的方法吗?
[解决办法]
http://hi.baidu.com/netcoder/item/65e9df10a74c17f89c778a15
[解决办法]
有用,我看看好使不
[解决办法]
不会,帮顶!
[解决办法]
DECLARE @DocHandle int
DECLARE @XmlDocument nvarchar(1000)
SET @XmlDocument = N'<polist>
<po><ponumber>100</ponumber><podate>2008-09-10</podate></po>
<po><ponumber>101</ponumber><podate>2008-09-11</podate></po>
</polist>'
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
SELECT * FROM OPENXML (@DocHandle, '/polist/po',2)
WITH (ponumber nvarchar(10),podate datetime)
EXEC sp_xml_removedocument @DocHandle
create table #aa(a varchar(8000))
insert into .....
关键怎处理XML?
LZ提供的XML格式,不符合XML标准格式喔.
这是ado数据集生成的xml
.removeAttribute "xmlns:s"
.removeAttribute "xmlns:dt"
End With
.Save "c:\tmp.xml"
变成如下格式:
<xmlxmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<rs:data>
<z:row no='AW20410 ' color='- ' plate='B' ID='8'/>
</rs:data>
</xml>
2. 再用 XSLT stylesheet 转成 C:\tmp.xml 如下 XML 格式.
<?xml version="1.0"?>
<xml>
<no>AW20410 </no>
<color>- </color>
<plate>B</plate>
<ID>B</ID>
</xml>
方法:
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<xsl:output method="xml" />
<xsl:template match="/">
<xsl:element name="product">
<xsl:for-each select="//rs:data/z:row">
<xsl:element name="no">
<xsl:value-of select="@no" />
<xsl:element name="color">
<xsl:value-of select="@color" />
<xsl:element name="plate">
<xsl:value-of select="@plate" />
<xsl:element name="ID">
<xsl:value-of select="@ID" />
</xsl:element>
</xsl:for-each>
</xsl:element>
</xsl:template>
</xsl:stylesheet>