what is wrong with my simple sqlxml query?

Question

Sorry,

Been struggle with this query for a while

declare @a xml
select @a='<GrsAutoCompleteCodes>
  <GrsAutoCompleteCode CitiCode="00DX" IsMatched="0" HasCustomName="0" />
  <GrsAutoCompleteCode CitiCode="00G0" IsMatched="0" HasCustomName="0" />
</GrsAutoCompleteCodes>'

SELECT       
 p.s.value('(CitiCode/text())[1]','VARCHAR(100)') AS CitiCode
FROM @a.nodes('/GrsAutoCompleteCode') p(s)

This query somehow returns no records, what am I doing wrongly?

Thom A · Accepted Answer · 2021-01-23 11:48:28Z

1

Because it's completely wrong, I'm afraid. Your root is GrsAutoCompleteCodes not GrsAutoCompleteCode, and CitiCode isn't a node it's a property of GrsAutoCompleteCode.

Perhaps you want this, which returns the value of CitiCode for the first GrsAutoCompleteCode node:

DECLARE @a xml;
SET @a = '<GrsAutoCompleteCodes>
  <GrsAutoCompleteCode CitiCode="00DX" IsMatched="0" HasCustomName="0" />
  <GrsAutoCompleteCode CitiCode="00G0" IsMatched="0" HasCustomName="0" />
</GrsAutoCompleteCodes>'

SELECT a.GACC.value('(GrsAutoCompleteCode/@CitiCode)[1]','varchar(4)') AS CitiCode
FROM @a.nodes('/GrsAutoCompleteCodes')a(GACC);

If you want the value of every CitiCode, that would be this:

SELECT a.GACC.value('@CitiCode','varchar(4)') AS CitiCode
FROM @a.nodes('/GrsAutoCompleteCodes/GrsAutoCompleteCode')a(GACC);

answered Jan 23, 2021 at 11:48

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

what is wrong with my simple sqlxml query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related