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 :

Gottfried Lesigang · Accepted Answer · 2018-10-02 08:16:48Z

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>

hkravitz · Accepted Answer · 2018-10-02 07:14:37Z

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

Ehsan Akbar · Accepted Answer · 2018-10-02 08:27:02Z

0

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

answered Oct 2, 2018 at 8:27

Ehsan Akbar

7,35723 gold badges106 silver badges201 bronze badges

1 Comment

Gottfried Lesigang Over a year ago

.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

Collectives™ on Stack Overflow

SQL Server Return null in xml column where condition

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related