3

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>

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Moni singh
  • 39
  • 1
  • 2
  • 2
    Please add your sql code..here is the link that can help ypi with start http://stackoverflow.com/questions/3989395/convert-xml-to-table-sql-server – Hiten004 Nov 09 '16 at 21:00

1 Answers1

6

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Perfect solution. Cross apply isn't required if column values are simple xml. – AlecZ Aug 25 '19 at 06:33
  • @AlecZ can you provide example of the same query without cross apply? – anoxis Aug 27 '19 at 06:00
  • @anoxis yes in my case a column (as xml) only had two levels in a column named "data": for each desired query row, there was a single "" tag with desired query columns such as "" as second-level tags. So e.g. SELECT name = data.value('(client/@name)[1]', 'varchar(50)') FROM clienttable – AlecZ Aug 27 '19 at 18:36