Home Forums Programming XML Need help getting XML data out of SQL table RE: Need help getting XML data out of SQL table

  • You can lose one cross apply and further simplify the query like this:

    SELECT

    o.value('(del_Nr/text())[1]', 'VARCHAR(10)') as PONbr,

    o.value('(del_Third_Nr/text())[1]', 'VARCHAR(10)') AS NCubeID,

    o.value('(Line/Product_Code/text())[1]', 'VARCHAR(30)') AS InvtID,

    o.value('(Line/Product_Quantity/text())[1]', 'INT') AS QtyOrd

    FROM #xNESP_XmlImportPOData

    CROSS APPLY xml_data.nodes('/message/body/shipmentorder/orderheader/data') a(o);

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001