直接看代码:
DECLARE @PORequestMessage XML
;WITH XMLNAMESPACES(DEFAULT 'http://soa.sina.com/SOA/USA/InfrastructureService/V30/PubSubService')
SELECT @PORequestMessage =
(
SELECT
FromService = N'http://CreatePOInterfanceService',
ToService = N'http://PubSubService',
RouteTable = (
SELECT
ArticleCategory = N'PurchaseOrder',
ArticleType1 = N'ACCT',
ArticleType2 = N'POCenter'
FOR XML PATH('Article'),TYPE --这样写Article结点也会带上命名空间。如果不想带,需要向下面的结点那样写。
),
[Node/MessageHead/Namespace] = N'' ,
[Node/MessageHead/Sender] = N'Acct',
[Node/Body/PORequest/RequestHeader/RequestTime/Date] = GETDATE(),
[Node/Body/PORequest/RequestHeader/RequestBy/UserID] = N'Acct',
[Node/Body/PORequest/RequestHeader/RequestSource] = N'ACCT',
[Node/Body/PORequest/RequestHeader/RequestMemo] = N'Acct Create POR for Debit Memo',
[Node/Body/PORequest/RequestHeader/Status] = N'@Status',
[Node/Body/PORequest/ContentNew] = N''
FOR XML PATH ('Publish'),TYPE
)
select @PORequestMessage
declare @PO xml
;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' as xsd,'http://www.w3.org/2001/XMLSchema-instance' as xsi )
select @po = ( SELECT TOP 1
[POHeader/InventoryOwnerCompanyCode] = N'1003', --default value
[POHeader/OrderAmount/@itemAmount] = N'0',
[POHeader/OrderAmount/@comAmount] = N'0',
[POHeader/OrderDate/Time] = GETDATE()
FOR XML PATH('PO'), TYPE
)
select @PO
SET @PORequestMessage.modify('declare default element namespace "http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService";
insert sql:variable("@PO")
into (/Publish/Node/Body/PORequest/ContentNew)[1] ')
select @PORequestMessage
注意modify方法中的第一句 declare default element namespace "http://soa.newegg.com/SOA/USA/InfrastructureService/V30/PubSubService";
如果没有这句的话,则插入结点失败。因为在第一个XML中是一个带有命名空间的XML。