50),
@xmlStr1 NVARCHAR(MAX) -
写在这里面的为 传入的参数
AS
BEGIN as begin后面的为自定义的变量
SET NOCOUNT ON;
DECLARE @xml XML =
@xmlStr1;
sqlserver操作XML
T-
SQL提供了以下几个针对XML类型的查询函数：
query(XQuery)：利用Xquery查询符合条件的元素集合。
value(XQuery,Type)：利用Xquery查询具体元素的值，Type定义值的类型。
exist(XQuery)：利用Xquery查询是否存在符合条件的元素。
nodes(XQuery)：利用Xquery获得一个结果集。
INSERT INTO qqtest(xmlinfo) xmlinfo 为字符串类型
VALUES(
N‘<root>
<person>
<id>F001</id>
<name>F001</name>
<age>
18</age>
</person>
</root>
‘
)
DECLARE @errorCode INT;
DECLARE @errorMsg VARCHAR(2000);
BEGIN TRY
SET @errorCode =
0;
SET @errorMsg =
‘‘;
DECLARE @xml XML;
SELECT @xml =
xmlinfo
FROM dbo.qqtest
WHERE id =
6;
SELECT S.value(‘id[1]‘,
‘varchar(30)‘) id,
S.value(‘name[1]‘,
‘varchar(30)‘) _Name,
S.value(‘age[1]‘,
‘varchar(30)‘) age
FROM @xml.nodes(‘/root/person‘) AS A(S);
//B(S) 都是没有问题的 @xml.nodes(‘/root/person‘) AS A(S) 当做一张表来操作了
SET @errorCode =
200;
END TRY
BEGIN CATCH
SET @errorCode =
500;
SET @errorMsg =
ERROR_MESSAGE();
RAISERROR(N‘error：XML文件格式错误：‘,
16,
1,@errorMsg);
PRINT(@errorMsg)
END CATCH
获取XML数据 ，把XML数据当做一个表格 @xml.nodes(‘/Root/data‘) AS T(S);
select S.value(
‘ID[1]‘,
‘int‘)
as ID, S.value(
‘Name[1]‘,
‘nvarchar(50)‘)
as Name
FROM @xml.nodes(‘/Root/data‘) AS T(S);
2 写入临时表
INSERT INTO #tbCRM_FranchiseeRecharge
(
[Recharge_ID],
[Franchisee_ID],
[Recharge_Credit],
[Recharge_Amount]
)
SELECT DISTINCT
NEWID(),
S.value(‘Franchisee_ID[1]‘,
‘uniqueidentifier‘) AS Franchisee_ID,
S.value(‘Recharge_Credit[1]‘,
‘int‘) AS Recharge_Credit,
S.value(‘Recharge_Amount[1]‘,
‘money‘) AS Recharge_Amount
FROM @xml.nodes(‘/Root/data‘) AS T(S);
2：SQl语句简单的 try catch 的统一格式：如下简单的Sql
string sql = @"BEGIN
SET @insertStateCode = 0;
IF NOT EXISTS
(
SELECT TOP 1
*
FROM tbCRM_Franchisee_Credit_History
WHERE Seq =@rechangeID
)
BEGIN TRY
BEGIN TRAN mytran;
INSERT INTO tbCRM_Franchisee_Credit_History
(
[Seq],
[Franchisee_ID],
[Tran_Code],
[Tran_By],
[Tran_Date],
[Credit],
[Amount],
[Remark]
)
SELECT RM.Recharge_ID,
RM.Franchisee_ID,
5,
RM.Create_By,
GETDATE(),
RM.Recharge_Credit,
RM.Recharge_Amount,
RM.Remark
FROM tbCRM_Franchisee_Recharge_Master RM
WHERE RM.Recharge_ID = @rechangeID;
UPDATE tbCRM_Franchisee_Recharge_Master
SET Approve_By = 1
WHERE Recharge_ID =@rechangeID;
SELECT @insertStateCode =200;
COMMIT TRAN mytran;
END TRY
BEGIN CATCH
SELECT @insertStateCode = 500;
ROLLBACK TRAN mytran;
END CATCH;
END;";
其实就是简单如下：
BEGIN TRY
BEGIN TRAN mytran;
END TRY
BEGIN CATCH
SELECT @insertStateCode = 500;
ROLLBACK TRAN mytran;
END CATCH;
SqlServer简单的操作XML以及SQl的 try catch等统一格式
标签：tst orm 存在 写入 select mon history his lse
