How to apply .nodes() to a column's cell instead of XML variable?

Question

Is there a way to apply a .node() method to a specific column instead of a XML variable? I'm working on transferring XML files into a SQL Table. Some of my XML files have child-nodes. So I'm using the Cross Apply function to connect the children with the parent.

For example, one of my columns in my table stores a child xml file. One cell contains the following XML: (Note I have pulled out the xml and already stored it in the @input variable).

DECLARE @input XML 
SET @INPUT = N'
    <ParentElement ID="1">
      <Title>parent1</Title>
      <Description />
      <ChildElement ID="6">
        <Title>Child 4</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
      <ChildElement ID="0">
        <Title>Child1</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
      <ChildElement ID="8">
        <Title>Child6</Title>
        <Description />
        <StartDate>2010-01-25T00:00:00</StartDate>
        <EndDate>2010-01-25T00:00:00</EndDate>
      </ChildElement>
    </ParentElement>'

This is what my Cross Apply looks like:

SELECT
    Parent.Elm.value('(@ID)[1]', 'int') AS 'ID',
    Parent.Elm.value('(Title)[1]', 'varchar(100)') AS 'Title',
    Parent.Elm.value('(Description)[1]', 'varchar(100)') AS 'Description',
    Child.Elm.value('(@ID)[1]', 'int') AS 'ChildID',
    Child.Elm.value('(Title)[1]', 'varchar(100)') AS 'ChildTitle',
    Child.Elm.value('(StartDate)[1]', 'DATETIME') AS 'StartDate',
    Child.Elm.value('(EndDate)[1]', 'DATETIME') AS 'EndDate'
FROM
    @input.nodes('/ParentElement') AS Parent(Elm)
CROSS APPLY
    Parent.Elm.nodes('ChildElement') AS Child(Elm)

Currently, I'm having to pull out that child-xml-node data from the XML cell and store it in a variable (@input) so that I can apply the .nodes() method to it. I would like to be able to simply call the nodes method on each particular cell in that column. Is there a way to do that?

Thanks for your help!

Credit to Beta033 and marc_s from this a link post about how to get the cross apply to work!

Answer 1

select 
    parent.value('(@ID)[1]', 'int') AS 'ID',
    parent.value('(Title)[1]', 'varchar(100)') AS 'Title',
    parent.value('(Description)[1]', 'varchar(100)') AS 'Description',
    child.value('(@ID)[1]', 'int') AS 'ChildID',
    child.value('(Title)[1]', 'varchar(100)') AS 'ChildTitle',
    child.value('(StartDate)[1]', 'DATETIME') AS 'StartDate',
    child.value('(EndDate)[1]', 'DATETIME') AS 'EndDate'    
 from 
      yourtable
           cross apply
      xmldata.nodes('/ParentElement') xp(parent)
           cross apply
      xp.parent.nodes('ChildElement') xc(child)

where xmldata is the field that contains the xml data in yourtable