XQuery and syntax of either query or value method to pull data from xml column

• Barret-330682

  SSC Rookie

  Points: 27

  More actions

  June 27, 2011 at 12:40 pm

  #240483

  What should the proper syntax be to pull the data back where value2 is yo5?

  SELECT AdditionalData.value('

  declare namespace C="test";

  (/C:DocumentElement/C:AdditionalData/C:Value2)[1]', 'VARCHAR(12)') AS answer

  from table

  I am trying to pull data from an xml data type column. The column when double clicked on looks like:

  <DocumentElement>

  <AdditionalData>

  <AdditionalDataID>1</AdditionalDataID>

  <DataCategoryID>1a</DataCategoryID>

  <Value1>yo1</Value1>

  <Value2>yo2</Value2>

  <Value3>yo3</Value3>

  </AdditionalData>

  <AdditionalData>

  <AdditionalDataID>2</AdditionalDataID>

  <DataCategoryID>2a</DataCategoryID>

  <Value1>yo4</Value1>

  <Value2>yo5</Value2>

  <Value3>yo6</Value3>

  </AdditionalData>

  </DocumentElement>

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 27, 2011 at 2:27 pm

  #1344816

  Barret-330682 (6/27/2011)

  ---

  What should the proper syntax be to pull the data back where value2 is yo5?

  SELECT AdditionalData.value('

  declare namespace C="test";

  (/C:DocumentElement/C:AdditionalData/C:Value2)[1]', 'VARCHAR(12)') AS answer

  from table

  I am trying to pull data from an xml data type column. The column when double clicked on looks like:

  <DocumentElement>

  <AdditionalData>

  <AdditionalDataID>1</AdditionalDataID>

  <DataCategoryID>1a</DataCategoryID>

  <Value1>yo1</Value1>

  <Value2>yo2</Value2>

  <Value3>yo3</Value3>

  </AdditionalData>

  <AdditionalData>

  <AdditionalDataID>2</AdditionalDataID>

  <DataCategoryID>2a</DataCategoryID>

  <Value1>yo4</Value1>

  <Value2>yo5</Value2>

  <Value3>yo6</Value3>

  </AdditionalData>

  </DocumentElement>

  Not sure what you mean by "the data". See if this gets you closer. If not, please clarify.

  DECLARE @xml XML = N'

  <DocumentElement>

  <AdditionalData>

  <AdditionalDataID>1</AdditionalDataID>

  <DataCategoryID>1a</DataCategoryID>

  <Value1>yo1</Value1>

  <Value2>yo2</Value2>

  <Value3>yo3</Value3>

  </AdditionalData>

  <AdditionalData>

  <AdditionalDataID>2</AdditionalDataID>

  <DataCategoryID>2a</DataCategoryID>

  <Value1>yo4</Value1>

  <Value2>yo5</Value2>

  <Value3>yo6</Value3>

  </AdditionalData>

  </DocumentElement>' ;

  SELECT @xml.query('for $ep in (/DocumentElement/AdditionalData)

  where $ep/Value2[1] eq "yo5"

  return <Values>

  { ($ep/Value2) }

  </Values>') ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• Barret-330682

  SSC Rookie

  Points: 27

  More actions

  June 27, 2011 at 3:57 pm

  #1344875

  thanks

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  June 27, 2011 at 3:59 pm

  #1344878

  You're welcome 🙂

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato