locked
Query XML with multiple elements RRS feed

  • Question

  • I have the following XML snippet (there will be multiple ScaleTicket elements in the XML file in the XML Column itself)

    <ScaleTicket Ticket="1000091" Assembly="156" Location="01">
        <DateEstablished>01/01/2000</DateEstablished>
        <LocationDesc><![CDATA[Reynolds United Co-op]]></LocationDesc>
        <CustomerId>100008</CustomerId>
        <GrossWeight>0</GrossWeight>
        <TareWeight>0</TareWeight>
        <NetWeight>0</NetWeight>
        <DockPercentage>0</DockPercentage>
        <ShrinkPercentage>0</ShrinkPercentage>
        <GrossUnits>3417.5</GrossUnits>
        <DockUnits>33</DockUnits>
        <ShrinkUnits>0</ShrinkUnits>
        <NetUnits>3384.5</NetUnits>
        <Comment><![CDATA[TD 3.6 CHMOGER]]></Comment>
        <BasePrice>0</BasePrice>
        <TotalPremDisc>0</TotalPremDisc>
        <TicketGradeFactor>
          <Grade>PROTEIN DISCOUNT/PREMIUM</Grade>
          <Value>14.6</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>SPRING WHEAT TEST WEIGHT DISC</Grade>
          <Value>57</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>CORN SHRINK</Grade>
          <Value>13.5</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>WHEAT DAMAGE</Grade>
          <Value>3.2</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
      </ScaleTicket>

    What I need to do is create 2 insert statements to take a portion of the XML file and insert it into one table and another portion of the XML file and insert it into another table. Here is the code I have come up with - the first Inssert/Select statement works great but the second Insert/Select statement the attributes I am trying to get show as null

    INSERT INTO SSIScaleTicket
    (
        DateEstablished,
        LocationDesc,
        CustomerId,
        GrossWeight,
        TareWeight,
        NetWeight,
        DockPercentage,
        ShrinkPercentage,
        GrossUnits,
        DockUnits,
        ShrinkUnits,
        NetUnits,
        Comment,
        BasePrice,
        Ticket,
        Assembly,
        Location
    )
    SELECT
        st.value('DateEstablished[1]', 'DATETIME') AS DateEstablished,
        st.value('LocationDesc[1]', 'VARCHAR(50)') AS LocationDesc,
        st.value('CustomerId[1]', 'VARCHAR(50)') AS CustomerId,
        st.value('GrossWeight[1]', 'VARCHAR(50)') AS GrossWeight,
        st.value('TareWeight[1]', 'VARCHAR(50)') AS TareWeight,
        st.value('NetWeight[1]', 'VARCHAR(50)') AS NetWeight,
        st.value('DockPercentage[1]', 'VARCHAR(50)') AS DockPercentage,
        st.value('ShrinkPercentage[1]', 'VARCHAR(50)') AS ShrinkPercentage,
        st.value('GrossUnits[1]', 'VARCHAR(50)') AS GrossUnits,
        st.value('DockUnits[1]', 'VARCHAR(50)') AS DockUnits,
        st.value('ShrinkUnits[1]', 'VARCHAR(50)') AS ShrinkUnits,
        st.value('NetUnits[1]', 'VARCHAR(50)') AS NetUnits,
        st.value('Comment[1]', 'VARCHAR(50)') AS Comment,
        st.value('BasePrice[1]', 'VARCHAR(50)') AS BasePrice,
        st.value('@Ticket', 'VARCHAR(50)') AS [Assembly],
        st.value('@Assembly', 'VARCHAR(50)') AS Location,
        st.value('@Location', 'VARCHAR(50)') AS Location
    FROM
        SSIGrainXMLDocs CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket') AS STElement(st);

    INSERT INTO SSITicketGradeFactor
    (
        Grade,
        [Value],
        PremDisc,
        Ticket,
        [Assembly],
        Location,
        CustomerId
    )
    SELECT
        tgf.value('Grade[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('Value[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('PremDisc[1]', 'VARCHAR(50)') AS PremDisc,
        st.value('./@Ticket', 'VARCHAR(50)') AS Ticket,
        st.value('./@Assembly', 'VARCHAR(50)') AS [Assembly],
        st.value('./@Location', 'VARCHAR(50)') AS Location,
        st.value('./CustomerId[1]', 'VARCHAR(50)') AS CustomerId
    FROM
        SSIGrainXMLDocs AS ST CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket') AS STElement(st),
        SSIGrainXMLDocs AS TGF CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket/TicketGradeFactor') AS TGFElement(tgf);


    I am using SQL Server 2005 where SSIGrainXMLDocs is the name of the table and GrainXmlFile is  the name of the XML column


    Thank you for your help!

    James
    Wednesday, December 24, 2008 3:21 PM

Answers

  • You normally apply different levels of CROSS APPLY as demonstrated here:

    http://blogs.technet.com/wardpond/archive/2006/02/19/database-programming-applying-apply-solving-parent-axis-access-performance-issues-in-xml-access-in-sql-server-2005.aspx


    It's hard to tell without some sample data.  Any chance you could reduce it to a small reproducible example, like "if I have this XML
    <sample></sample>

    how do I make it look like this?" 
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:25 PM
    Tuesday, January 6, 2009 10:33 PM
    Answerer
  • I got this to work:
    SELECT   
        a.b.value('Grade[1]''VARCHAR(50)' ) AS Grade,  
        a.b.value('Value[1]''VARCHAR(50)' ) AS Value,  
        a.b.value('PremDisc[1]''VARCHAR(50)' ) AS PremDisc,  
     
        x.y.value('@Ticket''VARCHAR(50)' ) AS Ticket,  
        x.y.value('@Assembly''VARCHAR(50)' ) AS Assembly,  
        x.y.value('@Location''VARCHAR(50)' ) AS Location,  
        x.y.value('CustomerId[1]''VARCHAR(50)' ) AS CustomerId  
     
    FROM @xml.nodes('ScaleTicket') x(y)  
        CROSS APPLY x.y.nodes('TicketGradeFactor') a(b) 

    It's sometimes helpful to map out the "addresses" of the elements and attributes you want, eg:

    ScaleTicket/TicketGradeFactor/Grade
    ScaleTicket/TicketGradeFactor/Value
    ScaleTicket/TicketGradeFactor/PremDisc
    ScaleTicket/@Ticket
    ScaleTicket/@Assembly
    ScaleTicket/@Location
    ScaleTicket/CustomerId

    As you can see, they have the root in common, and the first three columns are from the TicketGradeFactor child of the ScaleTicket element.  So in my query, the x(y) is "the ScaleTicket element and anything beneath it", and the a(b) bit is "the subset of x(y) query above, TicketGradeFactor only".  Looking at your example below, it looks like you are getting the idea.

    HTH
    wBob
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:24 PM
    Wednesday, January 7, 2009 7:23 PM
    Answerer
  • You can do it with dynamic SQL, but not directly:
    DECLARE @MFRXML XML  
    DECLARE @xmlPath NVARCHAR(255)  
    DECLARE @sql     NVARCHAR(MAX)  
     
    SET @xmlPath = 'c:\temp\temp.xml' 
     
    SET @sql = 'SELECT @MFRXML = BulkColumn FROM OPENROWSET(BULK N''' + @xmlPath + ''', SINGLE_BLOB) x'  
     
    EXEC sp_executesql @sql, N'@MFRXML XML OUT', @MFRXML OUT 
     
    SELECT @MFRXML  
     

    You could wrap that in a stored procedure.
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:45 PM
    Wednesday, January 7, 2009 9:36 PM
    Answerer

All replies

  • If I change the second/bottom SELECT statement to this - it just hangs and returns no data at all - i let it run for 30 minutes

    SELECT
        tgf.value('Grade[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('Value[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('PremDisc[1]', 'VARCHAR(50)') AS PremDisc,
        tgf.value('../@Ticket', 'VARCHAR(50)') AS Ticket,
        tgf.value('../@Assembly', 'VARCHAR(50)') AS [Assembly],
        tgf.value('../@Location', 'VARCHAR(50)') AS Location,
        tgf.value('../CustomerId[1]', 'VARCHAR(50)') AS CustomerId
    FROM
        SSIGrainXMLDocs AS TGF CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket/TicketGradeFactor') AS TGFElement(tgf);
    Wednesday, December 24, 2008 5:25 PM
  • Looks like I forgot to us the parenthesis around the attributes - this SQL works

    SELECT
        tgf.value('Grade[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('Value[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('PremDisc[1]', 'VARCHAR(50)') AS PremDisc,
        tgf.value('(../@Ticket)[1]', 'VARCHAR(50)') AS Ticket,
        tgf.value('(../@Assembly)[1]', 'VARCHAR(50)') AS [Assembly],
        tgf.value('(../@Location)[1]', 'VARCHAR(50)') AS Location,
        tgf.value('(../CustomerId)[1]', 'VARCHAR(50)') AS CustomerId
    FROM
        SSIGrainXMLDocs AS TGF CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket/TicketGradeFactor') AS TGFElement(tgf);
    • Marked as answer by jchaney88 Monday, January 5, 2009 8:46 PM
    • Unmarked as answer by jchaney88 Wednesday, January 7, 2009 9:25 PM
    Monday, January 5, 2009 8:46 PM
  • You should try and avoid use of Parent Axis ( .. ), as per the recommendations in:

    Performance Optimizations for the XML Data Type in SQL Server 2005
    http://msdn.microsoft.com/en-us/library/ms345118.aspx
    Monday, January 5, 2009 10:14 PM
    Answerer
  • Agreed but if I change it to

    SELECT
        tgf.value('Grade[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('Value[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('PremDisc[1]', 'VARCHAR(50)') AS PremDisc,
        tgf.value('(/Company/ScaleTicket/@Ticket)[1]', 'VARCHAR(50)') AS Ticket,
        tgf.value('(/Company/ScaleTicket/@Assembly)[1]', 'VARCHAR(50)') AS [Assembly],
        tgf.value('(/Company/ScaleTicket/@Location)[1]', 'VARCHAR(50)') AS Location,
        tgf.value('(/Company/ScaleTicket/CustomerId)[1]', 'VARCHAR(50)') AS CustomerId
    FROM
        SSIGrainXMLDocs AS TGF CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket/TicketGradeFactor') AS TGFElement(tgf);

    I don't get all the values - meaning the 3 attributes and one element are all from the first ScaleTicket it comes to. How would you then do the Select to get the attribute of the ScaleTicket that it is currently on?

    Thanks!
    Tuesday, January 6, 2009 9:47 PM
  • You normally apply different levels of CROSS APPLY as demonstrated here:

    http://blogs.technet.com/wardpond/archive/2006/02/19/database-programming-applying-apply-solving-parent-axis-access-performance-issues-in-xml-access-in-sql-server-2005.aspx


    It's hard to tell without some sample data.  Any chance you could reduce it to a small reproducible example, like "if I have this XML
    <sample></sample>

    how do I make it look like this?" 
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:25 PM
    Tuesday, January 6, 2009 10:33 PM
    Answerer
  • I just changed it to this and it works great

    SELECT
        tgf.value('(TicketGradeFactor/Grade)[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('(TicketGradeFactor/Value)[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('(TicketGradeFactor/PremDisc)[1]', 'VARCHAR(50)') AS PremDisc,
        tgf.value('@Ticket', 'VARCHAR(50)') AS Ticket,
        tgf.value('@Assembly', 'VARCHAR(50)') AS [Assembly],
        tgf.value('@Location', 'VARCHAR(50)') AS Location,
        tgf.value('CustomerId[1]', 'VARCHAR(50)') AS CustomerId
    FROM
        SSIGrainXMLDocs AS ST CROSS APPLY GrainXmlFile.nodes('/Company/ScaleTicket') AS TGFElement(tgf);
    Tuesday, January 6, 2009 10:52 PM
  • The solution I did previously only worked for the first instance of the element not all of them so I will answer your one question about how I want it to look. If I have the following XML

    <ScaleTicket Ticket="1000091" Assembly="156" Location="01">
        <DateEstablished>01/01/2000</DateEstablished>
        <LocationDesc><![CDATA[Reynolds United Co-op]]></LocationDesc>
        <CustomerId>100008</CustomerId>
        <GrossWeight>0</GrossWeight>
        <TareWeight>0</TareWeight>
        <NetWeight>0</NetWeight>
        <DockPercentage>0</DockPercentage>
        <ShrinkPercentage>0</ShrinkPercentage>
        <GrossUnits>3417.5</GrossUnits>
        <DockUnits>33</DockUnits>
        <ShrinkUnits>0</ShrinkUnits>
        <NetUnits>3384.5</NetUnits>
        <Comment><![CDATA[TD 3.6 CHMOGER]]></Comment>
        <BasePrice>0</BasePrice>
        <TotalPremDisc>0</TotalPremDisc>
        <TicketGradeFactor>
          <Grade>PROTEIN DISCOUNT/PREMIUM</Grade>
          <Value>14.6</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>SPRING WHEAT TEST WEIGHT DISC</Grade>
          <Value>57</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>CORN SHRINK</Grade>
          <Value>13.5</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
        <TicketGradeFactor>
          <Grade>WHEAT DAMAGE</Grade>
          <Value>3.2</Value>
          <PremDisc>0</PremDisc>
        </TicketGradeFactor>
      </ScaleTicket>

    I want the Select to return this result:

    Grade                                              Value      PremDisc         Ticket          Assembly        Location        CustomerId
    -------                                              --------     ------------      ------------      -------------      ------------       ----------------
    PROTEIN DISCOUNT/PREMIUM        14.6              0              1000091          156                  01                100008
    SPRING WHEAT TEST WEIGHT DISC 57                0              1000091          156                  01                100008
    CORN SHRINK                                   13.5            0               1000091          156                  01               100008
    WHEAT DAMAGE                                3.2              0               1000091          156                 01                100008

    Then the code would skip to the next ScaleTicket Element and do the same process.

    Thank you for your help!
    Wednesday, January 7, 2009 5:29 PM
  • Using the link you provided I came up with this

    SELECT
        tgf.value('(Grade/text())[1]', 'VARCHAR(50)') AS Grade,
        tgf.value('(Value/text())[1]', 'VARCHAR(50)') AS [Value],
        tgf.value('(PremDisc/text())[1]', 'VARCHAR(50)') AS PremDisc,
        st.value('@Ticket', 'VARCHAR(50)') AS Ticket,
        st.value('@Assembly', 'VARCHAR(50)') AS [Assembly],
        st.value('@Location', 'VARCHAR(50)') AS Location,
        st.value('(CustomerId/text())[1]', 'VARCHAR(50)') AS CustomerId
    FROM @MFRXML.nodes('/Company[1]') AS Company(comp)
    CROSS APPLY Company.comp.nodes('ScaleTicket') AS STElement(st)
    CROSS APPLY STElement.st.nodes('TicketGradeFactor') AS TGFElement(tgf);


    I declare the @MFRXML XML and do a bulkload with openrowset
    Wednesday, January 7, 2009 6:05 PM
  • I got this to work:
    SELECT   
        a.b.value('Grade[1]''VARCHAR(50)' ) AS Grade,  
        a.b.value('Value[1]''VARCHAR(50)' ) AS Value,  
        a.b.value('PremDisc[1]''VARCHAR(50)' ) AS PremDisc,  
     
        x.y.value('@Ticket''VARCHAR(50)' ) AS Ticket,  
        x.y.value('@Assembly''VARCHAR(50)' ) AS Assembly,  
        x.y.value('@Location''VARCHAR(50)' ) AS Location,  
        x.y.value('CustomerId[1]''VARCHAR(50)' ) AS CustomerId  
     
    FROM @xml.nodes('ScaleTicket') x(y)  
        CROSS APPLY x.y.nodes('TicketGradeFactor') a(b) 

    It's sometimes helpful to map out the "addresses" of the elements and attributes you want, eg:

    ScaleTicket/TicketGradeFactor/Grade
    ScaleTicket/TicketGradeFactor/Value
    ScaleTicket/TicketGradeFactor/PremDisc
    ScaleTicket/@Ticket
    ScaleTicket/@Assembly
    ScaleTicket/@Location
    ScaleTicket/CustomerId

    As you can see, they have the root in common, and the first three columns are from the TicketGradeFactor child of the ScaleTicket element.  So in my query, the x(y) is "the ScaleTicket element and anything beneath it", and the a(b) bit is "the subset of x(y) query above, TicketGradeFactor only".  Looking at your example below, it looks like you are getting the idea.

    HTH
    wBob
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:24 PM
    Wednesday, January 7, 2009 7:23 PM
    Answerer
  • Thanks for your help!! Your link you provided and the example you added were just what I needed!!

    I don't suppose you would know how to use a declared variable for the BULK path would you?

    Something like:

    DECLARE @MFRXML XML

    SELECT @MFRXML = BulkColumn
    FROM OPENROWSET(BULK N'''+@xmlPath+''', SINGLE_BLOB) TempXML

    where @xmlPath is declared a a varchar

    I keep getting this error:

    Cannot bulk load. The file "'+@xmlPath+'" does not exist.
    Wednesday, January 7, 2009 9:24 PM
  • You can do it with dynamic SQL, but not directly:
    DECLARE @MFRXML XML  
    DECLARE @xmlPath NVARCHAR(255)  
    DECLARE @sql     NVARCHAR(MAX)  
     
    SET @xmlPath = 'c:\temp\temp.xml' 
     
    SET @sql = 'SELECT @MFRXML = BulkColumn FROM OPENROWSET(BULK N''' + @xmlPath + ''', SINGLE_BLOB) x'  
     
    EXEC sp_executesql @sql, N'@MFRXML XML OUT', @MFRXML OUT 
     
    SELECT @MFRXML  
     

    You could wrap that in a stored procedure.
    • Marked as answer by jchaney88 Wednesday, January 7, 2009 9:45 PM
    Wednesday, January 7, 2009 9:36 PM
    Answerer
  • I can't thank you enough for ALL your help!!!

    THANK YOU VERY MUCH!!!
    Wednesday, January 7, 2009 9:45 PM