SQL Server Return null in xml column where condition

Question

I have this content in my XML Sql column as you can see :

<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>

I want to select all node with count=100

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

But when i run this query all value is null :

score 1 · Answer 1 · answered Oct 02 '18 at 07:14

Assuming that your XML structure is similar and only the values in the elements are different you can extract it using this 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

You would need `.nodes()` if there are repeating elements within the same XML. In this case your approach will have to deal with the overhead of creating a derived table... And btw: Using `text()` instead of `.` is a *best practice*. You might want to read [this](https://stackoverflow.com/a/43242238/5089204) — Shnugo, Oct 02 '18 at 08:36

score 1 · Accepted Answer · answered Oct 02 '18 at 08:16

If I get this correctly, there are many rows in your table, each row keeps one XML, each with the given structure. So the <Count> will exist exactly once per row. Correct?

if so, try this:

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;

UPDATE: Some words on your own code

Your code:

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

This will dive down to <Count> and will filter for any occurance of an attribute with this name and compare this to your introduced variable. This might work, if there was something like this

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

score 0 · Answer 3 · answered Oct 02 '18 at 08:27

0

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

answered Oct 02 '18 at 08:27

Ehsan Akbar

6,977
19
96
180

`.exist()` will speed up things only in cases, where there are multiple occurances and the engine can stop searching on the first match. And btw: Using `text()` is a *best practice*. You might want to read [this](https://stackoverflow.com/a/43242238/5089204) – Shnugo Oct 02 '18 at 08:38

SQL Server Return null in xml column where condition

3 Answers3