使用 XQuery 正確旋轉 XML 列

我想旋轉 SQL XML 列的屬性和值，並以有序的列和行格式顯示它。

首先，這是 SQL 中 XML 列的樣子：

<Model xmlns:dsc="ModelDescriptive" xmlns:draw="ModelDraw" xmlns:display="Display" xmlns:thd="Model3D">
 <dsc:Model name="15DE">
   <dsc:Defs>
     <dsc:Materials>
       <dsc:Material ref="E-HD-AnoAno" baseRef="E-HD" type="rod" productionGroup="OPTION" />
       <dsc:Material ref="E-3L44A-CLEAR" baseRef="E-3L44A" type="piece" productionGroup="OPTION" />
       <dsc:Material ref="R-TH01" baseRef="R-TH-TH" type="rod" productionGroup="ASSEMBLY" />
       <dsc:Material ref="M-TH05" baseRef="M-TH" type="meter" productionGroup="MOLDING" />
     </dsc:Materials>
   </dsc:Defs>
 </dsc:Model>
</Model>

…這是我的查詢：

SELECT
     x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
    ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
    ,x.y.value('.', 'VARCHAR(MAX)') Value
    ,Row_number() Over(Partition by x.y.value('local-name(..)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC)  as rn
FROM @xmlDescriptive.nodes('//*[text()], //@*') AS x(y)
WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
ORDER BY x.y.value('local-name(..)', 'VARCHAR(MAX)')

使用XQuery的**Nodes方法並僅從物料清單 (BOM) 中過濾材料，我得到了一個有趣的結果。

但是，我想以標準化的方式呈現數據。

我得到的第一個結果是這樣的：

然後我使用 Pivot 方法將此結果轉換為旋轉它。這是我嘗試轉換我的資訊的方法：

SELECT [ref], [baseRef], [type], [productionGroup]
FROM 
(
    SELECT
         x.y.value('local-name(..)', 'VARCHAR(MAX)') ParentElementName
        ,x.y.value('local-name(.)', 'VARCHAR(MAX)') Attribut
        ,x.y.value('.', 'VARCHAR(MAX)') Valeur
        ,CAST(Row_number() Over(Partition by x.y.value('local-name(.)', 'VARCHAR(MAX)') order by x.y.value('local-name(..)', 'VARCHAR(MAX)') ASC) as varchar(10)) as rn
    FROM @xmlDescriptive.nodes('//@*') AS x(y)
    WHERE x.y.value('local-name(..)', 'VARCHAR(MAX)')= 'Material'
   
) d
pivot
(
  max(Valeur)
  FOR Attribut in ([ref], [baseRef], [type], [productionGroup])
) piv 

我發現我的結果是無序的。列ref與我的**baseRef、type和productGroup列不匹配：

我有點困惑。我希望我的結果以這種方式格式化但已訂購。我的推理有什麼問題？

我希望我的結果如下所示：

我可能錯過了您的問題的重點，但這是我要獲得您正在尋找的結果的方法。

with xmlnamespaces('ModelDescriptive' as dsc)
select M.X.value('@ref', 'varchar(max)') as ref,
      M.X.value('@baseRef', 'varchar(max)') as baseRef,
      M.X.value('@type', 'varchar(max)') as type,
      M.X.value('@productionGroup', 'varchar(max)') as productionGroup
from @xmlDescriptive.nodes('//dsc:Material') as M(X);

您使用local-name()and//*[text()], //@*'建議您需要某種通用樞軸，但您Material在查詢中使用硬編碼的屬性名稱，所以我不知道您為什麼要走這條路。

引用自：https://dba.stackexchange.com/questions/292290