Search code examples
sql-serverxmlt-sqlxquery

what is wrong with my simple sqlxml query?

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?

Solution

  • 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);