110,499
社区成员
发帖
与我相关
我的任务
分享
/*
Borrowed from Kent's code
*/
declare @agent table
(
AgentID int,
Fname varchar(5),
SSN varchar(11)
)
insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'
declare @address table
(
AddressID int,
AddressType varchar(12),
Address1 varchar(20),
Address2 varchar(20),
City varchar(25),
AgentID int
)
insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3
/*
End Borrow
*/
SELECT
Tag,
Parent,
[Agents!1!],
[Agent!2!AgentID],
[Agent!2!Fname!Element],
[Agent!2!SSN!Element],
[AddressCollection!3!Element]
FROM (
SELECT
1 AS Tag,
NULL AS Parent,
0 AS Sort,
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element',
NULL AS 'AddressCollection!3!Element'
UNION ALL
SELECT
2 AS Tag, 1 AS Parent,
AgentID * 100 AS Sort,
NULL, AgentID, Fname, SSN,
NULL
FROM @agent
UNION ALL
SELECT
3 AS Tag, 2 AS Parent,
AgentID * 100 + 1 AS Sort,
NULL, NULL, NULL, NULL,
NULL
FROM @agent
) A
ORDER BY Sort
FOR XML EXPLICIT
alter Procedure Msg_ET_Order_Message_Batch_Insert(
@MsgArray xml
)
as
begin
DECLARE @docHandle int
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MsgArray;
INSERT INTO et_order_message
SELECT
MsgTitle 'MsgTitle',
REPLACE(MsgContent,'UserName',ISNULL(NULLIF(Cms_Member.UserName,''),'会员')) 'MsgContent',
UserID 'UserID',
OrderID 'OrderID',
INsertTime 'INsertTime',
IsRead 'IsRead',
MsgType 'MsgType',
MsgEx 'MsgEx',
IsCancel 'IsCancel'
FROM OPENXML(@docHandle, N'/Msg/Item',2)
WITH et_order_message LEFT JOIN CMS_MEMBER
ON UserID = CMS_MEMBER.T_ID
EXEC sp_xml_removedocument @docHandle
end
/*
参考示例
<?xml version="1.0" encoding="gb2312"?>
<Msg>
<Item>
<MsgTitle>7</MsgTitle>
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx>
</MsgEx>
<IsCancel>0</IsCancel>
</Item>
<Item>
<MsgTitle>7</MsgTitle>
<MsgContent>0</MsgContent>
<UserID>10000440</UserID>
<OrderID>495434</OrderID>
<INsertTime>2000/1/1 0:00:00</INsertTime>
<IsRead>0</IsRead>
<MsgType>0</MsgType>
<MsgEx>
</MsgEx>
<IsCancel>0</IsCancel>
</Item>
</Msg>
*/
select * from [表名] for xml RAW('ecsjsjhtxx')