Extract subtree from xml

soozh
soozh used Ask the Experts™
on
I have an xml declared as:
declare @xmlIndata xml = '<root>
  <FromDate>2018-04</FromDate>
  <ToDate>2019-08</ToDate>
  <Period />
  <Units>
    <Unit>
      <Name>1</Name>
    </Unit>
  </Units>
  <Variables>
    <Variable>
      <Name>N05C</Name>
    </Variable>
    <Variable>
      <Name>N06A</Name>
    </Variable>
    <Variable>
      <Name>PeopleCount</Name>
    </Variable>
  </Variables>
  <Filters />
</root>'

Open in new window


I want to extract the subtree starting at the node <Variables>.  So some xml manipulation to extract the subtree and set it to @variables. Like:

declare @variables xml
set @variables = somehow select the subtree 'Variables' from xmlIndata;

Then @variables should be:
 ' <Variables>
    <Variable>
      <Name>N05C</Name>
    </Variable>
    <Variable>
      <Name>N06A</Name>
    </Variable>
    <Variable>
      <Name>PeopleCount</Name>
    </Variable>
  </Variables>'

Open in new window

seems pretty basic to be able to extract part of an xml but i cant find out how to do it.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Are your tags correct? Isn't here a SQL Server tag missing?

T-SQL:

DECLARE @xmlIndata XML = N'<root>
  <FromDate>2018-04</FromDate>
  <ToDate>2019-08</ToDate>
  <Period />
  <Units>
    <Unit>
      <Name>1</Name>
    </Unit>
  </Units>
  <Variables>
    <Variable>
      <Name>N05C</Name>
    </Variable>
    <Variable>
      <Name>N06A</Name>
    </Variable>
    <Variable>
      <Name>PeopleCount</Name>
    </Variable>
  </Variables>
  <Filters />
</root>';

SELECT V.VariableName.value('.', 'NVARCHAR(255)') AS VariableName
FROM   @xmlIndata.nodes('/root/Variables/Variable/Name') V(VariableName);

Open in new window


When it's really node.js, then you should look into xml2js or xmldoc. Or you use regex, which should also work for this kind of XML layout.
soozhCEO

Author

Commented:
there is nothing wrong with the tags.  This is an xml i have in a stored procedure. I just want to get hold of the complete Variables subtree.
Software Team Lead
Commented:
try use xpath query, like this:

declare @xmlIndata xml = '<root>
  <FromDate>2018-04</FromDate>
  <ToDate>2019-08</ToDate>
  <Period />
  <Units>
    <Unit>
      <Name>1</Name>
    </Unit>
  </Units>
  <Variables>
    <Variable>
      <Name>N05C</Name>
    </Variable>
    <Variable>
      <Name>N06A</Name>
    </Variable>
    <Variable>
      <Name>PeopleCount</Name>
    </Variable>
  </Variables>
  <Filters />
</root>'

declare @xmlVariables xml
select @xmlVariables = @xmlIndata.query('//Variables')

select @xmlVariables

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial