asked on

Extract subtree from xml

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.

ste5an

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.

soozh

ASKER

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.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial