用xml字符作为参数（存储过程）

最新推荐文章于 2021-01-25 23:59:14 发布

最新推荐文章于 2021-01-25 23:59:14 发布 · 247 阅读

文章标签：

#XML #Exchange #F# #Office #SQL

sql server 专栏收录该内容

8 篇文章

订阅专栏

本文介绍了一个使用 SQL 的过程 SP_RPT_AP_BILLING_SUMMARY，该过程接收 XML 格式的参数，并通过 OPENXML 函数解析这些参数来查询 TMS_FREIGHT 和相关联表中的数据。此外，还演示了如何使用 sp_xml_preparedocument 和 OPENXML 处理 XML 数据。

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER PROCEDURE [dbo].[SP_RPT_AP_BILLING_SUMMARY]
@TMS_FREIGHT_IDS varchar(8000)				 ---FREIGHT_ID

WITH 
EXECUTE AS CALLER
AS

BEGIN

declare @idoc int

exec 	sp_xml_preparedocument @idoc output,@TMS_FREIGHT_IDS

SELECT TMS_FREIGHT_ID,FREIGHT_SOURCE
INTO #TMP_FRT
FROM TMS_FREIGHT 
WHERE TMS_FREIGHT_ID IN(
		select 	FRT_ID
		from openxml(@idoc,'/ROOT/FRT',2)
		with
		(
			FRT_ID		varchar(50) 'FRT_ID'
		)
)

exec sp_xml_removedocument @idoc


SELECT			
				F.TMS_FREIGHT_ID FRT_ID,
                 A.ATTEMPER_NO ATTEMPER_NO,
                 J.JOB_NO JOB_NO,
                 O.ORDER_NO ORDER_NO,
                 dbo.FN_GET_PERIOD (F.CREATED_DTM_LOC) FILE_PERIOD,
                 O.ORI_STATION ORI_STATION,
                 O.DEST_STATION DEST_STATION,
                 F.CREATED_OFFICE COST_INPUT_STATION,
                 F.BILLING_STATION PL_STATION,
                 F.CREATED_BY_USER CREATE_ID,
                 O.FINALLY_CARGO_PACKAGES QUANTITY,
                 O.FINALLY_CARGO_WEIGHT WEIGHT,
                 O.FINALLY_CARGO_CUBE CMB,
                 F.FRT_CODE ACE_CODE,
                 F.CURRENCY CUR,
                 F.EXCHANGE_RATE EXCHANGE_RATE,
                 F.UNIT_PRICE UNIT_PRICE
            FROM TMS_FREIGHT F
			LEFT JOIN TMS_ORDER O ON F.TMS_ORDER_ID = O.TMS_ORDER_ID
			LEFT JOIN TMS_ATTEMPER A ON F.TMS_ATTEMPER_ID = A.TMS_ATTEMPER_ID
			LEFT JOIN TMS_JOB J ON F.TMS_JOB_ID = J.TMS_JOB_ID
			WHERE RP_IND=1
			AND TMS_FREIGHT_ID IN(
				SELECT TMS_FREIGHT_ID
				FROM #TMP_FRT
				WHERE FREIGHT_SOURCE IN ('BK','JOB')
			) OR SHARE_FREIGHT_SOURCE_ID IN(
				SELECT TMS_FREIGHT_ID
				FROM #TMP_FRT
				WHERE FREIGHT_SOURCE NOT IN ('BK','JOB')
			)
			ORDER BY F.BILLING_STATION


END

传入的参数格式如下：


<ROOT>
  <FRT><FRT_ID>1231232</FRT_ID></FRT>
  <FRT><FRT_ID>1231233</FRT_ID></FRT>
  <FRT><FRT_ID>1231234</FRT_ID></FRT>
</ROOT>

xml操作测试


declare @xml varchar(1000)
set @xml = '<ArchiveMsg Title="jiangsuer" UserID="Admin1" Author="123" SendTime="1/1/2001" Department="mse" 
           AttachFile="hust"  ReceiveList="younther" Body="hustwelcome">
             <MsgKK>123123</MsgKK>
			 <MsgList UserID="Admin"/>
             <MsgList UserID="Anime"/>
             <MsgList UserID="Tiger"/>
          </ArchiveMsg> '

declare @idoc int

EXEC sp_xml_preparedocument @idoc output, @xml

SELECT Title,UserID,Author,SendTime,Department,ReceiveList,Body,AttachFile,MsgKK
FROM OpenXML(@idoc, '/ArchiveMsg')
WITH (
			Title		varchar(50),
			UserID		varchar(50),
			Author		varchar(50),
			SendTime	DATETIME,
			Department		varchar(50),
			ReceiveList		varchar(50),
			Body		varchar(50),
			AttachFile		varchar(50),
			MsgKK        varchar(50) 'MsgKK'
	  )

select MsgKK
from openxml(@idoc, '/ArchiveMsg')
with(
		MsgKK        varchar(50) 'MsgKK'
	)
SELECT '',UserID,''
FROM OpenXML(@idoc, '/ArchiveMsg/MsgList')
WITH  (
	MsgID   int,
	UserID  varchar(80),
	SendTime datetime
) 
EXEC sp_xml_removedocument @idoc