sql-server - 如何解析 SQL Server 2012 中的 XML 列？

Question

我从来没有在SQL Server中使用过XML解析，我想提取自己列中的字段，得到正确的数据。

我在 Customer 表中有一个名为 CustomerHeaderUncompressed 的列，如下所示，如何在 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

基本上是这样的:

• 从您的Customer 表中选择
• 使用 CROSS APPLY 和 XQuery .nodes() 函数将 XML 抓取为 XML 片段的“动态”伪表(表别名 XT，单列别名为XC)
• 使用 .value() XQuery 函数“到达”这些 XML 片段并提取您需要的值；像这样使用元素名称，属性需要以@符号作为前缀

试试这个并将其扩展到您的需要:

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)