Question

我有一个用户档案（基于用户类型的一个或多个档案），我将其存储在一个数据库列（XML格式）中。

我可以在我的存储过程中使用XPATH进行查询，但我不确定如何执行ORDER BY。

SELECT U.UserId, UP.Profile, UP.UserParentID
FROM aspnet_Users U
LEFT OUTER JOIN UserProperties UP ON U.UserId = UP.UserId
WHERE
UP.Profile.exist( /Properties/property[contains(.,sql:variable("@cLookup"))] ) = 1

示例XML：

<Properties>
            <property id="BusinessName" name="Business Name"></property>
            <property id="AccountNumber" name="Account Number"></property>
            <property id="Address" name="Address"></property>
            <property id="Phone" name="Phone"></property>
            <property id="Fax" name="Fax"></property>
            <property id="Web" name="Web"></property>
            <property id="ABN" name="ABN"></property>
            <property id="Logo" name="Logo"></property>
            <property id="Photos" name="Photos"></property>
            <property id="Map" name="Location Map"></property>
        </Properties>

和

<Properties>
            <property id="FirstName" name="First Name"></property>
            <property id="LastName" name="Last Name"></property>
        </Properties>

想按BusinessName排序。

Answer 1

考虑到这个答案仍在进行中，因为我不能确定这是否是正确的查询。

答案在使用 .nodes() 函数与 .value() 结合的时候。这样，您就可以从具有“id”属性值为“BusinessName”的元素中提取“name”属性的值。

这里有一点关于如何使用.nodes()替换旧的OPENXML语法的指南。（链接）

不管怎样，这是当前的查询。请在您的数据上尝试一下，看看我们是否可以调整它直到它起作用。

SELECT U.UserId, UP.Profile, UP.UserParentID
FROM aspnet_Users U
    LEFT OUTER JOIN UserProperties UP ON U.UserId = UP.UserId
    OUTER APPLY UP.Profile.nodes( /Properties/property ) p(prof)

WHERE UP.Profile.exist( /Properties/property[contains(.,sql:variable("@cLookup"))] ) = 1
    AND p.prof.value( @id ,  nvarchar(20) ) =  BusinessName 

ORDER BY p.prof.value( @name ,  nvarchar(100) )

Answer 2

另一个您可能想看看的选项是计算列，特别是如果您需要频繁使用一个或几个列。

通常情况下，计算列就是一个从其他值计算得出的列，它在任何时候都是最新的，无需频繁刷新。

与XML结合，您可以编写一个小的存储函数，并使用它在您的“基本”表上创建一个计算列，以便您可以查询和按该列排序，而无需一直“到达”XML。

在你的情况下，在UserProfile表上编写一个存储函数来检索“BusinessName”，并使其可用，如下所示：

CREATE FUNCTION dbo.GetBusinessName(@input XML)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS BEGIN
  DECLARE @Result VARCHAR(50)

  SELECT 
    @Result = @input.value( (Properties/property[@id="BusinessName"]/@name)[1] ,  VARCHAR(50) )

  RETURN @Result
END

这个定义了一个存储函数，它将使用你的个人资料XML并查找具有“Business Name” ID的“property”，并返回它的“name”属性。

要将此代码添加到您的UserProfile表中，请使用以下SQL代码：

ALTER TABLE UserProfile
    ADD BusinessName AS dbo.GetBusinessName(Profile) PERSISTED

通过这个，您可以将一个新的计算字段“BusinessName”添加到您的表中，从现在开始，您可以使用这个新字段选择和排序，例如。

SELECT ID, BusinessName FROM UserProfiles ORDER BY BusinessName

享受！

PS：关于性能的一点说明：我发现在许多情况下，这比不断查询XML字段中的XML要快得多。因此，如果您的XML中有某些元素需要经常访问，或者可能出现在ORDER BY语句中，使用计算列实际上可以显着加快速度。

友情链接