Querying XML Datatype Column

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  July 28, 2008 at 8:21 am

  #194645

  with the xml below, how can i use a select statement to extract

  the option name?

  this is the first time im exposed to xml in a db so any help is appreciated.

  thanks

  

• sql_prodigy

  SSCrazy

  Points: 2254

  More actions

  July 28, 2008 at 9:20 am

  #849235

  anyone?

• Chad Crawford

  SSChampion

  Points: 11610

  More actions

  July 28, 2008 at 1:47 pm

  #849403

  Does this get you started in the right direction?

  DECLARE @myvar AS XML

  , @XMLDoc int

  SELECT @myvar = '

  &ltMovement xmlns="" Context="Acquire"&gt

  &ltOperationCollection&gt

  &ltOperation Name = "Execute"&gt

  &ltOptionCollection&gt

  &ltOption Name="Execution.Capability"&gt(Synchronous,Asynchronous)&lt/Option&gt

  &ltOption Name="Execution.Preference"&gt(Asynchronous)&lt/Option&gt

  &ltOption Name="Origin.Status.Criteria"&gt(Fail)&lt/Option&gt

  &lt/OptionCollection&gt

  &lt/Operation&gt

  &ltOperation Name="ConnectorContext"&gt

  &ltOptionCollection&gt

  &ltOption Name="Connector.MerchantIdentifier"&gt56500&lt/Option&gt

  &ltOption Name="Connector.Alias"&gtCC_01&lt/Option&gt

  &ltOption Name="Connector.Wallet.TransactionIdentifier"&gt(wirecard:Transaction)(00000000000000000000000001xP2)()&lt/Option&gt

  &ltOption Name="Connector.TrasactionIdentifier"&gt(wirecard:Transaction)C242720181323966504820)()&lt/Option&gt

  &ltOption Name="Connector.Context"&gtwirecard&lt/Option&gt

  &lt/OptionCollection&gt

  &lt/Operation&gt

  &lt/OperationCollection&gt

  &lt/Movement&gt

  '

  EXEC sp_xml_preparedocument @XMLDoc OUTPUT, @myvar

  SELECT *

  FROM OPENXML(@XMLDoc, '/Movement/OperationCollection/Operation/OptionCollection/Option', 1)

  WITH (NAME varchar(100) '@Name')

  Hope this helps!

  Chad