MSSQL查询Xml差异顺序

Question

当用户发送请求时，我的数据库需要存储xml请求，而且xml有两个通信元素，WP和SA，但它们不是固定的(我指的是位置)：

    <bms:OrgInfo>
      <bms:Communications>
        <bms:CommQualifier>WP</bms:CommQualifier>
        <bms:CommPhone>5555551212</bms:CommPhone>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>SA</bms:CommQualifier>
        <bms:Address>
          <bms:Address1>1234 Test Avenue</bms:Address1>
          <bms:Address2>1234 Test Avenue</bms:Address2>
          <bms:City>Alamogordo</bms:City>
          <bms:StateProvince>NM</bms:StateProvince>
          <bms:PostalCode>88310</bms:PostalCode>
          <bms:CountryCode>US</bms:CountryCode>
        </bms:Address>
      </bms:Communications>
    </bms:OrgInfo>

有时，SA元素将是第一个。

如何在不担心中毒的情况下查询？

下面是我当前的查询，我使用索引来查询它：

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlStore]') AND type in (N'U'))
DROP TABLE [dbo].[XmlStore]
GO

CREATE TABLE [dbo].[XmlStore](
    [XmlRequest] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [XmlStore]([XmlRequest]) VALUES('<?xml version="1.0" encoding="UTF-8"?>
    <bms:OrgInfo xmlns:bms="http://example.org">
      <bms:Communications>
        <bms:CommQualifier>WP</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>SA</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>EM</bms:CommQualifier>
      </bms:Communications>
    </bms:OrgInfo>')


;WITH xmlnamespaces ( 'http://example.org' AS bms )
Select
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications/bms:CommQualifier)[1]', 'nvarchar(100)') as WP_Value,
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications/bms:CommQualifier)[2]', 'nvarchar(100)') as SA_Value,
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications/bms:CommQualifier)[3]', 'nvarchar(100)') as EM_Value
FROM [XmlStore] AS t    

DROP TABLE [dbo].[XmlStore]

如果用户重新订购通信元素，那将是一场灾难！

Answer 1

您可以在这里添加一个XQuery predicate

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[XmlStore]') AND type in (N'U'))
DROP TABLE [dbo].[XmlStore]
GO

CREATE TABLE [dbo].[XmlStore](
    [XmlRequest] [xml] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

INSERT INTO [XmlStore]([XmlRequest]) VALUES('<?xml version="1.0" encoding="UTF-8"?>
    <bms:OrgInfo xmlns:bms="http://example.org">
      <bms:Communications>
        <bms:CommQualifier>WP</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>SA</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>EM</bms:CommQualifier>
      </bms:Communications>
    </bms:OrgInfo>')

- XML再次出现，但顺序不同

INSERT INTO [XmlStore]([XmlRequest]) VALUES('<?xml version="1.0" encoding="UTF-8"?>
    <bms:OrgInfo xmlns:bms="http://example.org">
      <bms:Communications>
        <bms:CommQualifier>SA</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>EM</bms:CommQualifier>
      </bms:Communications>
      <bms:Communications>
        <bms:CommQualifier>WP</bms:CommQualifier>
      </bms:Communications>
    </bms:OrgInfo>')

-查询(所有索引都是[1])

;WITH xmlnamespaces ( 'http://example.org' AS bms )
Select
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications[bms:CommQualifier="WP"]/bms:CommQualifier)[1]', 'nvarchar(100)') as WP_Value,
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications[bms:CommQualifier="SA"]/bms:CommQualifier)[1]', 'nvarchar(100)') as SA_Value,
    t.XmlRequest.value('(/bms:OrgInfo/bms:Communications[bms:CommQualifier="EM"]/bms:CommQualifier)[1]', 'nvarchar(100)') as EM_Value
FROM [XmlStore] AS t    

DROP TABLE [dbo].[XmlStore]