我在嘗試將一些 XML 資料（在舊的 MS SQL Server 2012 中存盤為文本）決議并轉換為可用格式時遇到了一些問題。
XML 資料是一個字串，但是當我將其轉換為 XML 時，它看起來像這樣：
<?xml version="1.0" encoding="utf-8"?>
<header1>
<header2>
<OrderFormHeader>
<AccountNum>123456</AccountNum>
<OrderNum>000123987</OrderNum>
<OrderDetails>
<CompanyName>Biznez1</CompanyName>
<CompAddressInfo>
<City>Phoenix</City>
<State>AZ</State>
</CompAddressInfo>
<ShipTo>TRUE</ShipTo>
<BillTo>FALSE</BillTo>
</OrderDetails>
</OrderFormHeader>
<OrderFormDetails>
<OrderFormLines>
<ItemNum>000001</ItemNum>
<InventoryNum>INV-001-000001</InventoryNum>
<OtherDetails>
<QtyOrdered>1</QtyOrdered>
<ItemDesc>Bandaids</ItemDesc>
<UnitofMeasure>Box</UnitofMeasure>
<ItemCode>
<CodeType>UPC</CodeType>
<CodeID>123456789123</CodeID>
</ItemCode>
<OtherDetails>
</OrderFormLines>
</OrderFormDetails>
<OrderFormLines>
<ItemNum>000002</ItemNum>
<InventoryNum>INV-001-000002</InventoryNum>
<OtherDetails>
<QtyOrdered>1</QtyOrdered>
<ItemDesc>QTips</ItemDesc>
<UnitofMeasure>Box</UnitofMeasure>
<ItemCode>
<CodeType>UPC</CodeType>
<CodeID>123456789987</CodeID>
</ItemCode>
<OtherDetails>
</OrderFormLines>
<OrderFormLines>
<ItemNum>000003</ItemNum>
<InventoryNum>INV-003-000001</InventoryNum>
<OtherDetails>
<QtyOrdered>1</QtyOrdered>
<ItemDesc>Scissors</ItemDesc>
<UnitofMeasure>Each</UnitofMeasure>
<ItemCode>
<CodeType>UPC</CodeType>
<CodeID>123456987321</CodeID>
</ItemCode>
<OtherDetails>
</OrderFormLines>
</header2>
</header1>
不用說，這是一個瘋狂的 XML（至少對我而言）。（注意：物件中嵌套了多組 OrderFormDetails 并通過我的代碼決議它們似乎在 ItemNum 和 InventoryNum 上散開。我已經洗掉了 UPC 代碼內容，因為這會導致額外的散開，但不介意把它帶回我的代碼）
話雖如此，我當前的 SQL 代碼使用表變數從表中獲取資料，更正 UTF-8 并將其轉換為 XML 格式。從那里，我使用 CROSS APPLY 函式來獲取資料，但它有嚴重的扇出問題，它會多次顯示資料，而不僅僅是每行 1 行：
DECLARE @xml TABLE (IMPORTED_XML xml)
INSERT INTO @xml
SELECT
CAST(REPLACE(mxt.XML_FIELD,'encoding="UTF-8"','encoding="UTF-16"') AS XML) AS IMPORTED_XML
FROM MyXMLTable as mxt
with temp1 AS (
SELECT DISTINCT
sales_order.value('(./AccountNum/text())[1]','nvarchar(max)') AS ACCOUNT_NUM
, sales_order.value('(./OrderNum/text())[1]','nvarchar(max)') AS ORDER_NUM
, extra_so.value('(./CompanyName/text())[1]','nvarchar(max)') AS COMPANY_NAME
, base.value('(./ItemNum/text())[1]','nvarchar(max)') AS ITEM_ID
, base.value('(./InventoryNum/text())[1]','nvarchar(max)') AS INVENTORY_NUM
, sales.value('(./QtyOrdered/text())[1]','nvarchar(max)') AS QTY_ORDERED
, sales.value('(./UnitofMeasure/text())[1]','nvarchar(max)') AS ITEM_UOM
, sales.value('(./ItemDesc/text())[1]','nvarchar(max)') AS ITEM_DESC
FROM @xml
CROSS APPLY IMPORTED_XML.nodes('/header1/header2') AS core(core)
CROSS APPLY core.nodes('//OrderFormDetails/OrderFormLines') as base(base)
CROSS APPLY core.nodes('//OrderFormHeader') AS sales_order(sales_order)
CROSS APPLY base.nodes('//OtherDetails') as sales(sales)
CROSS APPLY sales_order.nodes('//OrderDetails') AS extra_so(extra_so)
CROSS APPLY sales.nodes('//ItemCode') as itmcode(itmcode)
)
select * from temp1 order by item_desc asc
這似乎主要作業，但它最終會為相同的東西提供多行資料......我習慣于在雪花中使用橫向展平功能，但在 SQL Server 2012 中沒有使用這種 XML 決議。對此有任何見解嗎？預先感謝您的幫助
uj5u.com熱心網友回復：
您的問題是您從根一直交叉連接每個嵌套節點，因為您使用的是//.
還有其他幾點需要注意：
- 您不需要臨時表，您可以
CROSS APPLY在一個查詢中將所有內容放在一起 REPLACE如果該列已經是varchar，則不需要，僅當它是nvarchar.- 您不需要
.nodes在每個級別的嵌套上都使用它，只有在您想要一個級別的多個專案時才需要它。 - 仔細選擇您的資料型別，一切都必須如此
nvarchar(max)嗎？
SELECT
sales_order.value('(AccountNum/text())[1]','varchar(50)') AS ACCOUNT_NUM
, sales_order.value('(OrderNum/text())[1]','varchar(50)') AS ORDER_NUM
, sales_order.value('(OrderDetails/CompanyName/text())[1]','nvarchar(200)') AS COMPANY_NAME
, base.value('(ItemNum/text())[1]','varchar(50)') AS ITEM_ID
, base.value('(InventoryNum/text())[1]','varchar(50)') AS INVENTORY_NUM
, sales.value('(QtyOrdered/text())[1]','int') AS QTY_ORDERED
, sales.value('(UnitofMeasure/text())[1]','varchar(20)') AS ITEM_UOM
, sales.value('(ItemDesc/text())[1]','nvarchar(max)') AS ITEM_DESC
, itmcode.value('(CodeType/text())[1]','varchar(20)') AS itemcodetype
, itmcode.value('(CodeID/text())[1]','varchar(50)') AS itemcodeID
FROM MyXMLTable as mxt
CROSS APPLY (VALUES( CAST(REPLACE(mxt.XML_FIELD,'encoding="UTF-8"','encoding="UTF-16"') AS xml) )) v(IMPORTED_XML)
CROSS APPLY IMPORTED_XML.nodes('/header1/header2') AS core(core)
CROSS APPLY core.nodes('OrderFormHeader') AS sales_order(sales_order)
CROSS APPLY core.nodes('OrderFormDetails/OrderFormLines') as base(base)
CROSS APPLY base.nodes('OtherDetails') as sales(sales)
CROSS APPLY sales.nodes('ItemCode') as itmcode(itmcode);
資料庫<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/378870.html