主页 / dba / 问题 / 292290
Accepted
JF Landry
Asked: 2021-05-27 04:10:20 +0800 CST

使用 XQuery 正确旋转 XML 列

  • 772

我想旋转 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列不匹配：

在此处输入图像描述

我有点困惑。我希望我的结果以这种方式格式化但已订购。我的推理有什么问题？

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

在此处输入图像描述

sql-server-2014 pivot

1 个回答

  1. Best Answer

    我可能错过了您的问题的重点，但这是我要获得您正在寻找的结果的方法。

    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在查询中使用硬编码的属性名称，所以我不知道您为什么要走这条路。

    • 5

相关问题