How to parse XML column in SQL Server 2012?

Question

I have never used XML parsing in SQL Server, I would like to extract the fields in its own column, get the correct data.

I have a column called CustomerHeaderUncompressed in a Customer table that looks something like below, how do I extract the fields and the data in SQL Server 2012?

<CustomerHeaderData>
<CustomerHeader>
    <shippingmethod Value="00000000-0000-0000-0000-000000000000" Name="" />
    <discount Value="" />
    <customdiscount Value="0" />
    <ponumber Value="9909933793" />
    <tax1 Value="-1" />
    <tax2 Value="-1" />
    <tax3 Value="0" />
    <tax3name Value="" />
    <tax4 Value="0" />
    <Freight />
    <ClientExtraField6 Value="5" />
    <ClientExtraField7 Value="3" />
    <dateneeded Value="01/01/0001 00:00:00" />
    <ClientTaxCodeSource>0</ClientTaxCodeSource>
    <shippingbranch />
    <dropnumber Value="" />
    <comment Value="" />
    <shippingzone Value="" />
    <salespersonID Value="704e78d4-cdbb-4963-bcc2-2c83a1d5f3fd" />
    <salesperson Value="Salesrep, XYZ" />
    <installation Value="False" />
    <salesterms Value="18" />
    <HeldItemDeliveryMethod Value="0" />
    <customcontrol>
        <CustomCustomerHeader CultureInfo="en-US">
            <BusinessSegment>TR</BusinessSegment>
            <BusinessSegmentID>1</BusinessSegmentID>
            <OrderType>2</OrderType>
            <MarketSegment>S3</MarketSegment>
            <CustomerDeliveryDate>2010-01-21</CustomerDeliveryDate>
            <BuildingPermitNumber />
            <FinalWallDepth />
            <PricingType>2</PricingType>
            <HouseBuiltBefore1978>False</HouseBuiltBefore1978>
            <AttributePricing>False</AttributePricing>
            <UndeterminedAttributes>False</UndeterminedAttributes>
            <EventIDStatus>VerifyFailed</EventIDStatus>
            <EventIDEnabled>False</EventIDEnabled>
            <CustomerDiscount>0</CustomerDiscount>
            <PreparedBy />
            <RequestedShipDate>01/14/2010</RequestedShipDate>
            <UserTestDate>01/01/0001</UserTestDate>
        </CustomCustomerHeader>
    </customcontrol>
</CustomerHeader>

Answer 1

Basically something like this:

• select from your Customer table
• use CROSS APPLY and the XQuery .nodes() function to grab the XML as a "on-the-fly" pseudo table of XML fragments (table alias XT, single column aliassed as XC)
• "reach" into those XML fragments and pull out the values you need, using the .value() XQuery function; use element names as such, and attributes need to be prefixed with a @ sign

Try this and extend it to your needs:

SELECT 
    ShippingMethodValue = XC.value('(shippingmethod/@Value)[1]', 'varchar(50)'),
    ShippingMethodName = XC.value('(shippingmethod/@Name)[1]', 'varchar(50)'),
    DiscountValue = XC.value('(discount/@Value)[1]', 'varchar(50)'),
    CustomDiscountValue = XC.value('(customdiscount/@Value)[1]', 'varchar(50)'),
    PONumber= XC.value('(ponumber/@Value)[1]', 'bigint' )
FROM
    Customer
CROSS APPLY
    CustomerHeaderUncompressed.nodes('/CustomerHeaderData/CustomerHeader') AS XT(XC)