我需要幫助將 XML 變數決議為如下表格格式。我是決議的新手，在谷歌中查找并能夠決議AccountId，，但無法決議具有 in 屬性的節點，例如，EmailProductNameProductTypeCustNameCreatedDatebillingCode
DECLARE @xmlvalue XML =
'<Request>
<AccountId>16000</AccountId>
<Email>[email protected]</Email>
<Attributes>
<Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">CustName</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">TestName</Value></Attribute>
<Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">CreatedDate</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">3/26/2022</Value></Attribute>
<Attribute><Name xmlns="http://testtarget.com/wsdl/myAPI">BillingCode</Name><Value xmlns="http://testtarget.com/wsdl/myAPI">testbiling</Value></Attribute>
</Attributes>
<ProductName>TestProduct</ProductName>
<ProductType>Recurring</ProductType>
</Request>'
預期輸出：
AccountId Email CustName CreatedDate BillingCode ProductName ProductType
1600 test@gmail.com TestName 03/26/2022 testbiling TestProduct Recurring
下面的代碼給了我想要的東西。有沒有簡單的方法？
SELECT req.req_col.value('AccountId[1]','INT') AS AccountId,
req.req_col.value('Email[1]','VARCHAR(100)') AS Email,
req.req_col.value('ProductName[1]','VARCHAR(100)') AS ProductName,
req.req_col.value('ProductType[1]','VARCHAR(100)') AS ProductType,
v.CustName,
v.CreatedDate,
v.BillingCode
FROM @xmlvalue.nodes('/Request') req(req_col)
CROSS APPLY ( SELECT *
FROM (SELECT req.req_col.value('Name[1]','VARCHAR(100)') AS Name,
req.req_col.value('Value[1]','VARCHAR(100)') AS Value
FROM @xmlvalue.nodes('/Request/Attributes/Attribute') req(req_col)
) tab1
PIVOT (
MAX(Value) FOR Name IN ( CustName, CreatedDate, BillingCode )) Tab2
) v
uj5u.com熱心網友回復：
不必為此使用PIVOT，您可以在 XPath 運算式中使用過濾器來選擇各種屬性名稱-值元素對，例如：
with xmlnamespaces (
'http://testtarget.com/wsdl/myAPI' as anything
)
select
request.value(N'(AccountId/text())[1]', N'nvarchar(50)') as AccountId,
request.value(N'(Email/text())[1]', N'nvarchar(50)') as Email,
request.value(N'(Attributes/Attribute[anything:Name/text()="CustName"]/anything:Value/text())[1]', N'nvarchar(50)') as CustName,
request.value(N'(Attributes/Attribute[anything:Name/text()="CreatedDate"]/anything:Value/text())[1]', N'nvarchar(50)') as CreatedDate,
request.value(N'(Attributes/Attribute[anything:Name/text()="BillingCode"]/anything:Value/text())[1]', N'nvarchar(50)') as BillingCode,
request.value(N'(ProductName/text())[1]', N'nvarchar(50)') as ProductName,
request.value(N'(ProductType/text())[1]', N'nvarchar(50)') as ProductType
from @xmlvalue.nodes('/Request') foo(request);
| 帳戶ID | 電子郵件 | 客戶名稱 | 創建日期 | 計費代碼 | 產品名稱 | 產品類別 |
|---|---|---|---|---|---|---|
| 16000 | [email protected] | 測驗名稱 | 2022 年 3 月 26 日 | 測驗計費 | 測驗產品 | 再次發生的 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/456572.html
上一篇：從記錄中獲取最近3個月