在SQL server中使用 XML 实例：

最新推荐文章于 2024-08-13 22:40:40 发布

otong

最新推荐文章于 2024-08-13 22:40:40 发布

阅读量712

点赞数

分类专栏： SQL Server 文章标签： xml sql server path website insert email

本文链接：https://blog.csdn.net/otong/article/details/6312088

版权

SQL Server 专栏收录该内容

24 篇文章 0 订阅

订阅专栏

-- 简单的生成一个XML的格式内容.

DECLARE @LogMsg XML SET @LogMsg = ( SELECT @GiftCardCode AS 'GiftCardCode' ,'CREA' AS 'Action' ,@InUser AS 'InUser' ,@FromSystem AS 'FromSystem' ,@Memo AS 'Memo' ,'1.0' AS 'Version' FOR XML PATH('Body'),TYPE)

--带有属性值的构造方法。

select 'en'    as "English/@xml:lang",
       'food'  as "English",
       'ger'   as "German/@xml:lang",
       'Essen' as "German",       
       'Female' as "who/@Gender", --属性值的构造。
       'Angelia' as who
for xml path (N'')

--一条记录生成一个xml结点

declare @vendorMaster xml

;WITH XMLNAMESPACES( DEFAULT 'http://soa.amazon.com/SOA/USA/SqlQueryService/V10/QueryVendorMaster') SELECT @vendorMaster = (SELECT top 2Vendor.TransactionNumber,Vendor.CompanyCode,ISNULL(RTRIM(vendornumber),'') AS [VendorNumber],ISNULL(RTRIM(VendorName),'') AS [VendorName],ISNULL(RTRIM(DBA),'') AS [DBA],ISNULL(RTRIM(Address1),'') AS [Address1],ISNULL(RTRIM(Address2),'') AS [Address2],ISNULL(RTRIM(City),'') AS [City],ISNULL(RTRIM([State]),'') AS [State],ISNULL(RTRIM(ZipCode),'') AS [ZipCode],ISNULL(RTRIM(Country),'') AS [Country],RTRIM(WebSite) AS [WebSite],ISNULL(RTRIM(TaxId),'') AS [TaxId],ISNULL(RTRIM(PaytermsCode),'') AS [PaytermsCode],ISNULL(RTRIM(PhoneNumber),'') AS [PhoneNumber],ISNULL(RTRIM(FaxNumber),'') AS [FaxNumber],ISNULL(RTRIM(Email),'') AS [Email],(CASE WHEN Vendor.paymethod in ('C', 'T', 'A') THEN RTRIM(Vendor.paymethod) ELSE 'C' END) AS [PayMethod]FROM dbo.SAP_VendorMaster Vendor (NOLOCK)--INNER JOIN @VendorList b--ON Vendor.transactionnumber = b.transactionnumberFOR XML AUTO,ELEMENTS )

--生成复杂XML

DECLARE @Message XML;WITH XMLNAMESPACES(DEFAULT 'http://soa.amazon.com/SOA/USA/InfrastructureService/V30/PubSubService')--or ;WITH XMLNAMESPACES('http://www.w3.org/2001/XMLSchema' as xsd,'http://www.w3.org/2001/XMLSchema-instance' as xsi ) SELECT @Message = (SELECT 'OrderProcessResultService' AS "FromService",'PubSubService' AS "ToService", [POHeader/ApproveUser] = rtrim(approve_user), [POHeader/ApproveDate] = approve_date, [POHeader/LastEditTime/Time] = GETDATE(), [POItems] = N''[RouteTable] =(SELECTA.ArticleCategory,A.ArticleType1,A.ArticleType2FROM (SELECT 'SellerPortal' AS "ArticleCategory",'CreditRequestStatus' AS "ArticleType1",'*' AS "ArticleType2") AS AFOR XML PATH('Article'),TYPE),[Node/Body] = (SELECT top 1a.EDIID,a.TransactionNumber,a.SellerID,FROM dbo.CreditRequest AS a WITH(NOLOCK)FOR XML PATH('CreditRequest'), TYPE)FOR XML PATH ('Publish'),TYPE)

--解析XML的方法1：

SELECT @CustomerNumber = @Msg.value('(/Body/CustomerNumber/text())[1]',N'INT')

--解析XML的方法2：

DECLARE @XML XML, @FedexClaimNumber INT, @ClaimNumber INT, SET @XML = CONVERT(XML,@XMLCliam) DECLARE@TrackingNumber char(50); SELECT TOP(1) @TrackingNumber = T.c.value('(TrackingNumber/text())[1]','char(50)') ,@ClaimNumber = T.c.value('(ClaimNumber/text())[1]','int') FROM @XML.nodes('/Claim') AS T(c)

--修改结点，向结点插入值

SET @PORequestMessage.modify(' declare default element namespace "http://soa.amazon.com/SOA/USA/InfrastructureService/V30/PubSubService";

insert sql:variable("@PO")

into (/Publish/Node/Body/PORequest/ContentNew)[1] ')

--插入一个结点：

declare @poTran xml

select @poTran = ( select

ItemCode = rtrim(item),

[Description] = rtrim(descrip),

from #POTransaction

FOR XML PATH('Item'),TYPE )

SET @PO.modify('

insert sql:variable("@poTran")

into (/PO/POItems)[1] ')

/*
BYDBA 1 解析XML时获取单一值时，不要使用nodes()
DECLARE @xml xml
SET @xml = N'text1
<c>text2
<d>text3
<e>text4</e>
</d>
<f>text5</f>
</c>
'

SELECT
@xml.value('(/b/c/d/text())[1]','char(5)')
,@xml.value('(/b/c/f/text())[1]','char(5)')
*/

/*About namespace 命名空间
DECLARE @xml xml
SET @xml = N'text1
<c>text2
<d xmlns="https://soa.neweg.com">text3
<e>text4</e>
</d>
<f>text5</f>
</c>
'

;WITH xmlnamespaces(DEFAULT 'https://soa.newegg.com/dba'
,'https://soa.neweg.com' AS ns)

SELECT
@xml.value('(/b/c/text())[1]','char(5)') AS xml_col_val
,@xml.value('(/b/c/ns:d/text())[1]','char(5)') AS xml_col_va2
*/

/*retrieve value 取单个值
DECLARE @xml xml
SET @xml = N'text1
<c>text2
<d>text3
<e>text4</e>
</d>
<f>text5</f>
</c>
'
SELECT @xml.value('(/b/c/text())[1]','char(5)') AS xml_col_val
*/