問題描述
我有這個 xml:
<viewNode xsi:type="View:Projection" name="Projection_1">
<endUserTexts label=" "/>
<element name="CITY">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0"/>
</element>
<element name="ROAD_ID">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0"/>
</element>
<element name="LEN">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0"/>
</element>
<element name="CITY2">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0"/>
</element>
<element name="F">
<inlineType name="INTEGER" primitiveType="INTEGER" length="0" precision="0" scale="0"/>
</element>
<elementFilter elementName="F">
<valueFilter xsi:type="Column:SingleValueFilter" including="true" value="1"/>
</elementFilter>
<input>
<viewNode xsi:type="View:JoinNode">#/0/Join_1</viewNode>
<mapping xsi:type="Type:ElementMapping" targetName="CITY" sourceName="CITY"/>
<mapping xsi:type="Type:ElementMapping" targetName="ROAD_ID" sourceName="ROAD_ID"/>
<mapping xsi:type="Type:ElementMapping" targetName="LEN" sourceName="LEN"/>
<mapping xsi:type="Type:ElementMapping" targetName="CITY2" sourceName="CITY2"/>
<mapping xsi:type="Type:ElementMapping" targetName="F" sourceName="F"/>
</input></viewNode>
這是我檢索數(shù)據(jù)的代碼:
and this is my code to retrieve the data:
SELECT
Tab.Col.value('../@name','nvarchar(50)') as ViewNode,
Tab.Col.value('@name','nvarchar(50)') as Name,
Tab.Col.value('(endUserTexts/@label)[1]','nvarchar(50)') as Label,
Tab.Col.value('(inlineType/@primitiveType)[1]','nvarchar(50)') as PrimitveType,
Tab.Col.value('(inlineType/@length)[1]','nvarchar(50)') as Length,
Tab.Col.value('(inlineType/@precision)[1]','nvarchar(50)') as Precision,
Tab.Col.value('(inlineType/@scale)[1]','nvarchar(50)') as Scale
FROM @x.nodes('/viewNode/element') AS Tab(Col)
這種方式有效并檢索每個元素的數(shù)據(jù)，但我還想從 elementFilter 檢索數(shù)據(jù)，并將其視為另一個元素.我的問題是，有沒有辦法檢索父節(jié)點 viewNode 的所有子節(jié)點?
This way works and retrieves the data for each element, but I would like to also retrieve the data from elementFilter, and treat it as if it were another element. My question is, there is a way to retrieve all the child nodes of the parent node viewNode?
類似于FROM @x.nodes('/viewNode/ANYCHILDNODE') AS Tab(Col)
推薦答案
首先:提供的示例不完整，因為缺少命名空間 xsi 的聲明.在我的示例中，我添加了一個虛擬聲明...
First of all: The provided example cannot be complete as there is a declaration for the namespace xsi missing. In my example I've added a dummy declaration...
這種方式有效并檢索每個元素的數(shù)據(jù)，但我會還喜歡從 elementFilter 中檢索數(shù)據(jù)，并將其視為如果它是另一個元素.我的問題是，有一種方法可以檢索父節(jié)點viewNode的所有子節(jié)點?就像是FROM @x.nodes('/viewNode/ANYCHILDNODE') AS Tab(Col)
This way works and retrieves the data for each element, but I would like to also retrieve the data from
elementFilter, and treat it as if it were another element. My question is, there is a way to retrieve all the child nodes of the parent nodeviewNode? Something likeFROM @x.nodes('/viewNode/ANYCHILDNODE') AS Tab(Col)
在這種情況下，最好提供預期的輸出...
In such cases it was best to provide the expected output...
完全是另一回事，包括與 1:n 相關的
<element> and <elementFilter> do not share the same attributes. And <input> is something else entirely, including a 1:n related <mapping> array itself...
所以:是的，有 * 的意思是 ANYCHILD.像 /viewNode/* 這樣的 XPath 將返回 local-name() 對元素的名稱做出正常的反應.在下面的代碼中，我向 XPath 添加了一個 substring predicate 以返回以短語 element 開頭的元素.這將返回 .試試看:
So: Yes, there is * meaning ANYCHILD. An XPath like /viewNode/* will return all children below <viewNode>. You can then use local-name() to react on an element's name gerically. In the following clode I add a substring predicate to the XPath in order to return elements starting with the phrase element. This will return <element> and <elementFilter> but will ignore <input>. Try it out:
DECLARE @x XML=
N'<viewNode xmlns:xsi="dummy" xsi:type="View:Projection" name="Projection_1">
<endUserTexts label=" " />
<element name="CITY">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0" />
</element>
<element name="ROAD_ID">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0" />
</element>
<element name="LEN">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0" />
</element>
<element name="CITY2">
<inlineType primitiveType="CHAR" length="0" precision="0" scale="0" />
</element>
<element name="F">
<inlineType name="INTEGER" primitiveType="INTEGER" length="0" precision="0" scale="0" />
</element>
<elementFilter elementName="F">
<valueFilter xsi:type="Column:SingleValueFilter" including="true" value="1" />
</elementFilter>
<input>
<viewNode xsi:type="View:JoinNode">#/0/Join_1</viewNode>
<mapping xsi:type="Type:ElementMapping" targetName="CITY" sourceName="CITY" />
<mapping xsi:type="Type:ElementMapping" targetName="ROAD_ID" sourceName="ROAD_ID" />
<mapping xsi:type="Type:ElementMapping" targetName="LEN" sourceName="LEN" />
<mapping xsi:type="Type:ElementMapping" targetName="CITY2" sourceName="CITY2" />
<mapping xsi:type="Type:ElementMapping" targetName="F" sourceName="F" />
</input>
</viewNode>';
--注意命名空間...
--Beware of the namespace...
WITH XMLNAMESPACES('dummy' AS xsi)
SELECT
Tab.Col.value('../@name','nvarchar(50)') as ViewNode,
Tab.Col.value('@name','nvarchar(50)') as Name,
Tab.Col.value('(endUserTexts/@label)[1]','nvarchar(50)') as Label,
Tab.Col.value('(inlineType/@primitiveType)[1]','nvarchar(50)') as PrimitveType,
Tab.Col.value('(inlineType/@length)[1]','nvarchar(50)') as Length,
Tab.Col.value('(inlineType/@precision)[1]','nvarchar(50)') as Precision,
Tab.Col.value('(inlineType/@scale)[1]','nvarchar(50)') as Scale,
Tab.Col.value('@elementName','nvarchar(50)') as filter_elementName,
Tab.Col.value('(valueFilter/@xsi:type)[1]','nvarchar(50)') as filter_ValueFilterType,
Tab.Col.value('(valueFilter/@including)[1]','bit') as filter_Including,
Tab.Col.value('(valueFilter/@value)[1]','nvarchar(50)') as filter_value
FROM @x.nodes('/viewNode/*[substring(local-name(),1,7)="element"]') AS Tab(Col)
這篇關于從父節(jié)點 xml sql server 檢索所有子節(jié)點的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！