Post reply

XML XQuery shred into fully encapsulated individual rows

  • February 25, 2019 at 1:41 pm

    #416728

    I cant seem to work out a query that returns fully encapsulated shredded xml.

    This will get me very close, but missing the parent node, and anything at the second level (L1ID,L1Name)

    drop table #temp
    Declare @xmlContent xml
    set @xmlContent = cast('<Jobs><L1ID>151</L1ID><L1Name>Roles</L1Name><L1Data><L2ID>17</L2ID>
    <L2Name>Advisor</L2Name><L2Data><L3ID>1</L3ID><L3Name>Advise stuff</L3Name></L2Data></L1Data><L1Data>
    <L2ID>18</L2ID><L2Name>Carpentor</L2Name><L2Data><L3ID>2</L3ID><L3Name>Carpentor stuff</L3Name>
    </L2Data></L1Data><L1Data><L2ID>18</L2ID><L2Name>Laborer</L2Name><L2Data><L3ID>5</L3ID>
    <L3Name>Laborer stuff</L3Name></L2Data></L1Data></Jobs>' as XML)

    Select @xmlContent as xmlContent into #temp

    SELECT
    c.query('.')
    FROM #temp
    CROSS APPLY xmlcontent.nodes('/Jobs/L1Data') as T (C)

    Original XML
    <Jobs>
    <L1ID>151</L1ID>
    <L1Name>Roles</L1Name>
    <L1Data>
      <L2ID>17</L2ID>
      <L2Name>Advisor</L2Name>
      <L2Data>
      <L3ID>1</L3ID>
      <L3Name>Advise stuff</L3Name>
      </L2Data>
    </L1Data>
    <L1Data>
      <L2ID>18</L2ID>
      <L2Name>Carpentor</L2Name>
      <L2Data>
      <L3ID>2</L3ID>
      <L3Name>Carpentor stuff</L3Name>
      </L2Data>
    </L1Data>
    <L1Data>
      <L2ID>18</L2ID>
      <L2Name>Laborer</L2Name>
      <L2Data>
      <L3ID>5</L3ID>
      <L3Name>Laborer stuff</L3Name>
      </L2Data>
    </L1Data>
    </Jobs>

    Desired Result Set (rows returned)
    Row 1
    <Jobs>                 --<-- Required but missing
    <L1ID>151</L1ID>             --<-- Required but missing
    <L1Name>Roles</L1Name>           --<-- Required but missing
    <L1Data>
      <L2ID>17</L2ID>
      <L2Name>Advisor</L2Name>
      <L2Data>
      <L3ID>1</L3ID>
      <L3Name>Advise stuff</L3Name>
      </L2Data>
    </L1Data>
    </Jobs>

    Row 2
    <Jobs>
    <L1Data>
      <L2ID>18</L2ID>
      <L2Name>Carpentor</L2Name>
      <L2Data>
      <L3ID>2</L3ID>
      <L3Name>Carpentor stuff</L3Name>
      </L2Data>
    </L1Data>
    </Jobs>

    Row 3 
    <Jobs>
    <L1Data>
      <L2ID>18</L2ID>
      <L2Name>Laborer</L2Name>
      <L2Data>
      <L3ID>5</L3ID>
      <L3Name>Laborer stuff</L3Name>
      </L2Data>
    </L1Data>
    </Jobs>
    Any questions ill try to answer asap! thanks 

  • February 26, 2019 at 6:03 am

    #2022957

    Not sure if this is what you want, it returns the non-L1Data sibling nodes for all rows

    SELECT
    c.query('
            let $a := .
            return <Jobs>{ ../*[(local-name()!="L1Data")][. << $a], . }</Jobs>'
        )
    FROM #temp
    CROSS APPLY xmlcontent.nodes('/Jobs/L1Data') as T (C)

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • February 26, 2019 at 8:28 am

    #2022978

    Perfect!!

    Where can I learn more about what you have inside the query() ?

  • February 26, 2019 at 8:31 am

    #2022979

    There's a ton of info here
    https://docs.microsoft.com/en-us/sql/xquery/xquery-language-reference-sql-server?view=sql-server-2017

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply