none
Break down XML files using Microsoft SQL into individual rows

    Question

  • Here's what I'm doing. I'm inserting an XML file into an XML field, along with other fields such as date stamps etc. for logging. (Those are irrelevant to this request)

    The file looks like something similar to this:

     

    <topLevelItem>
      <uselessInformation>
      </uselessInformation>
      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
    <topLevelItem> 
    

     

    My goal is to be able to query the XML field and get a result set with each in it's own row, in it's XML form. Such as below:

    Row 1:

      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
    

    Row 2:

     

      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
    

     

    Row 3:

     

      <secondLevelItem> 
        <secondLevelItemDetail> 
        </secondLevelItemDetail> 
        <secondLevelItemAnotherDetail> 
        </secondLevelItemAnotherDetail> 
      </secondLevelItem> 
    

     

    This has to be done using Microsoft SQL Server and without using CLI. I need to be able to declare which XML node I want the break down to start at, as some files have additional fields that I don't require. So something like, WHERE node = secondLevelItem.  Also, if possible, this may need to be done without schema files.

    • Edited by BenInJapan Tuesday, January 10, 2012 3:15 AM typos
    Tuesday, January 10, 2012 3:08 AM

All replies

  • Try the below:

    declare @x xml
    set @x = '<topLevelItem>
      <uselessInformation>
      </uselessInformation>
      <secondLevelItem>
        <secondLevelItemDetail>
        </secondLevelItemDetail>
        <secondLevelItemAnotherDetail>
        </secondLevelItemAnotherDetail>
      </secondLevelItem>
      <secondLevelItem>
        <secondLevelItemDetail>
        </secondLevelItemDetail>
        <secondLevelItemAnotherDetail>
        </secondLevelItemAnotherDetail>
      </secondLevelItem>
      <secondLevelItem>
        <secondLevelItemDetail>
        </secondLevelItemDetail>
        <secondLevelItemAnotherDetail>
        </secondLevelItemAnotherDetail>
      </secondLevelItem>
    </topLevelItem>'

    SELECT T.c.query('.') AS result
    FROM   @x.nodes('/topLevelItem/secondLevelItem') T(c);

    Tuesday, January 10, 2012 6:01 AM
  • That gets me most of the way there.  But I need to be able to declare the node dynamically, with a variable.

    For example:

    SELECT T.c.query('.') AS result
    FROM @x.nodes(@node) T(c);

     

    Any ideas?

    Wednesday, January 11, 2012 3:58 AM
  • Here you go:

    declare @v varchar(500)
    set @v = 'secondLevelItem'

    SELECT T.c.query('.') AS result
    FROM   @x.nodes('/topLevelItem/*') T(c)
    where T.c.value('local-name(.)','varchar(500)') = @v

    Hope this is what you required.

    • Proposed as answer by tsathiyan Wednesday, January 11, 2012 6:27 AM
    Wednesday, January 11, 2012 6:27 AM