retrieve all child nodes from a parent node xml sql server

Question

I have this 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>

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)

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?

Something like FROM @x.nodes('/viewNode/ANYCHILDNODE') AS Tab(Col)

If the child nodes were in a hierarchical order, then by starting from top to bottom using CROSS APPLY http://www.kodyaz.com/t-sql/xml-in-sql-server-hierarchy-levels-cross-apply.aspx you can get all desired item level data. But since these are in same level, you can simply use two queries and use UNION ALL — Eralper, Oct 10 '18 at 10:38
I think you can also add and for eliminating error due to missing definition in the XML — Eralper, Oct 10 '18 at 10:39
it does have it, i just copied part of the xml to simplify the question — Rob, Oct 10 '18 at 11:20

score 2 · Answer 1 · answered Oct 15 '18 at 10:33

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...

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? Something like FROM @x.nodes('/viewNode/ANYCHILDNODE') AS Tab(Col)

In such cases it was best to provide the expected output...

<element> and <elementFilter> do not share the same attributes. And <input> is something else entirely, including a 1:n related <mapping> array itself...

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)

retrieve all child nodes from a parent node xml sql server

1 Answers1

Linked