如何在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>

- Moni singh

---

请在这里添加您的SQL代码。这是一个可以帮助您入门的链接：https://dev59.com/tG865IYBdhLWcg3wFqnY - Hiten004

Answer 1

基本上像这样：

• 从你的Customer表中选择
• 使用CROSS APPLY和XQuery.nodes()函数以"即时"伪表的形式抓取XML片段（表别名为XT，单列别名为XC）
• "访问"这些XML片段并使用.value() XQuery函数提取所需值；使用元素名称，属性需要加上@符号作为前缀

尝试一下，根据你的需求进行扩展：

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

---

完美解决方案。如果列值是简单的 XML，就不需要使用 CROSS APPLY。 - AlecZ

@AlecZ，你能提供一个不使用CROSS APPLY的相同查询的示例吗？ - anoxis

@anoxis 是的，在我的情况下，一个列（作为xml）只有两个级别，在名为"data"的列中：对于每个所需的查询行，都有一个单独的"<client>"标签，其中包含所需的查询列，例如"<name>"作为第二级标签。因此，例如，从clienttable中选择 name = data.value('(client/@name)[1]', 'varchar(50)') - AlecZ