Sql-Server-2014
使用 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
在查詢中使用硬編碼的屬性名稱,所以我不知道您為什麼要走這條路。