34,594
社区成员
发帖
与我相关
我的任务
分享
create procedure test
@XML varchar(max) ,
@xx varchar(20),
……,
……
as
Declare @sql varchar(max)
set @sql='insert into tb(XX,……,xmls)
select '''+@xx+''','''+……+……''',''','''+@XML+''''
exec(@sq)
DECLARE @XmlDocumentHandle int
DECLARE @XmlDocument nvarchar(4000)
SET @XmlDocument = N'<ROOT>
<Customer>
<CustomerID>VINET</CustomerID>
<ContactName>Paul Henriot</ContactName>
<Order OrderID="10248" CustomerID="VINET" EmployeeID="5" OrderDate="1996-07-04T00:00:00">
<OrderDetail ProductID="11" Quantity="12"/>
<OrderDetail ProductID="42" Quantity="10"/>
</Order>
</Customer>
<Customer>
<CustomerID>LILAS</CustomerID>
<ContactName>Carlos Gonzlez</ContactName>
<Order OrderID="10283" CustomerID="LILAS" EmployeeID="3" OrderDate="1996-08-16T00:00:00">
<OrderDetail ProductID="72" Quantity="3"/>
</Order>
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @XmlDocumentHandle OUTPUT, @XmlDocument
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer',2)
WITH (CustomerID varchar(10),ContactName varchar(20))
SELECT *
FROM OPENXML (@XmlDocumentHandle, '/ROOT/Customer/Order',1)
WITH (OrderID varchar(10),CustomerID varchar(10), EmployeeID int,OrderDate datetime)
EXEC sp_xml_removedocument @XmlDocumentHandle
/*
CustomerID ContactName
VINET Paul Henriot
LILAS Carlos Gonzlez
OrderID CustomerID EmployeeID OrderDate
10248 VINET 5 1996-07-04 00:00:00.000
10283 LILAS 3 1996-08-16 00:00:00.000
*/
DECLARE @TMPXML VARCHAR(MAX)
SET @TMPXML =replace(@XML,'''','tszf')
拼接的时候,用@TMPXML 代替@XML。就是单引号出问题。其他的不会。
之后再执行:
UODATE TB SET XML=REPLACE (XML,'tszf','''')