代码之家 › 专栏 › 技术社区 › Ehsan Akbar

SQL Server在xml列where condition中返回null

xml sql-server

Ehsan Akbar · 技术社区 · 6 年前

如您所见,我的XML Sql列中有以下内容:

<LabelRequestInputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Count>100</Count>
  <ConfirmedCountByMunicipality xsi:nil="true" />
  <ConfirmedCountByProvincialGovernment xsi:nil="true" />
  <ConfirmedCountBySymfaExpert xsi:nil="true" />
  <TypeOfLabelDelivery>Post</TypeOfLabelDelivery>
  <TypeOfLabel>Public</TypeOfLabel>
  <NextState>Municipality</NextState>
</LabelRequestInputParameters>

我想选择计数为100的所有节点

    declare @a nvarchar(max)
  set @a='100'
  select InputParameter.value(N'(/LabelRequestInputParameters/Count[@Count=(sql:variable("@a"))])[1]', 'Bigint') from Requests

但当我运行此查询时,所有值都为空:

3 回复 | 直到 6 年前

Gottfried Lesigang 6 年前

如果我理解正确的话,表中有很多行,每行保留一个XML,每个XML都具有给定的结构。所以 <Count> 每行只存在一次。对的?

如果是,请尝试以下操作:

DECLARE @SomeTable TABLE(YourXML XML);
INSERT INTO @SomeTable VALUES
(N'<LabelRequestInputParameters>
  <Count>100</Count>
  <test>test 100</test>
</LabelRequestInputParameters>')
,(N'<LabelRequestInputParameters>
  <Count>200</Count>
  <test>test 200</test>
</LabelRequestInputParameters>');

SELECT *
FROM @SomeTable
WHERE YourXML.value('(/LabelRequestInputParameters/Count/text())[1]','int') = 100;

更新:关于你自己的代码的一些词

您的代码:

.value(N'(/LabelRequestInputParameters/Count[@Count=(sql:variable("@a"))])[1]', 'Bigint')

这将下降到 < 属性并将其与引入的变量进行比较。如果真有这样的事,这可能管用

<Count Count="100">100</Count>

hkravitz 6 年前

假设您的XML结构相似,只有元素中的值不同,您可以使用以下XQuery提取它:

DECLARE @a INT = 100
DECLARE @Tbl TABLE (XmlCol XML) 
INSERT INTO @Tbl
VALUES (
 '<LabelRequestInputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Count>100</Count>
  <ConfirmedCountByMunicipality xsi:nil="true" />
  <ConfirmedCountByProvincialGovernment xsi:nil="true" />
  <ConfirmedCountBySymfaExpert xsi:nil="true" />
  <TypeOfLabelDelivery>Post</TypeOfLabelDelivery>
  <TypeOfLabel>Public</TypeOfLabel>
  <NextState>Municipality</NextState>
</LabelRequestInputParameters>'
) ,

(
 '<LabelRequestInputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Count>100</Count>
  <ConfirmedCountByMunicipality xsi:nil="true" />
  <ConfirmedCountByProvincialGovernment xsi:nil="true" />
  <ConfirmedCountBySymfaExpert xsi:nil="true" />
  <TypeOfLabelDelivery>Post</TypeOfLabelDelivery>
  <TypeOfLabel>Public</TypeOfLabel>
  <NextState>Municipality</NextState>
</LabelRequestInputParameters>'
),

(
 '<LabelRequestInputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Count>150</Count>
  <ConfirmedCountByMunicipality xsi:nil="true" />
  <ConfirmedCountByProvincialGovernment xsi:nil="true" />
  <ConfirmedCountBySymfaExpert xsi:nil="true" />
  <TypeOfLabelDelivery>Post</TypeOfLabelDelivery>
  <TypeOfLabel>Public</TypeOfLabel>
  <NextState>Municipality</NextState>
</LabelRequestInputParameters>'
),
(
 '<LabelRequestInputParameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <Count>150</Count>
  <ConfirmedCountByMunicipality xsi:nil="true" />
  <ConfirmedCountByProvincialGovernment xsi:nil="true" />
  <ConfirmedCountBySymfaExpert xsi:nil="true" />
  <TypeOfLabelDelivery>Post</TypeOfLabelDelivery>
  <TypeOfLabel>Public</TypeOfLabel>
  <NextState>Municipality</NextState>
</LabelRequestInputParameters>'
)

SELECT  q.Cnt
FROM @Tbl
    CROSS APPLY XmlCol.nodes ('/LabelRequestInputParameters/Count') l(x)
    CROSS APPLY 
        (
        VALUES (l.x.value ('.','varchar(1000)'))
        ) Q(Cnt)

WHERE q.Cnt = @a

Ehsan Akbar 6 年前

   SELECT InputParameter.value(N'(/LabelRequestInputParameters/Count)[1]','bigint')
    FROM Requests
    WHERE InputParameter.exist(N'/LabelRequestInputParameters/Count[.="100"]')=1
    GO