XQUERY syntax question; how do i extract the GUID?

• Theo Ekelmans

  SSCarpal Tunnel

  Points: 4592

  More actions

  June 18, 2015 at 11:10 am

  #99005

  Hi,

  I'm trying to extract a GUID from a table in an eVault DB.

  The XML looks like this

  <includeSelectionItemsTemp>

  <ArrayOfObjectSelector xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">

  <ObjectSelector>

  <startLocation xmlns="urn:agent-management-data-structures">/$VSPHERE$/$VM$/</startLocation>

  <displayLocation xmlns="urn:agent-management-data-structures">

  <name />

  <displayName>/</displayName>

  <localize>false</localize>

  <type>UNKNOWN</type>

  </displayLocation>

  <displayLocation xmlns="urn:agent-management-data-structures">

  <name>$VSPHERE$</name>

  <displayName>$VSPHERE$</displayName>

  <localize>false</localize>

  <type>UNKNOWN</type>

  </displayLocation>

  <displayLocation xmlns="urn:agent-management-data-structures">

  <name>$VM$</name>

  <displayName>$VM$</displayName>

  <localize>false</localize>

  <type>UNKNOWN</type>

  </displayLocation>

  <separator xmlns="urn:agent-management-data-structures">/</separator>

  <specs xmlns="urn:agent-management-data-structures">

  <name>502a383c-85e8-7737-1c15-4ece21fe1d03;b3JkYmVoMDAz</name>

  <displayName>ordbeh003</displayName>

  <localize>false</localize>

  <type>VSPHERE_VM</type>

  </specs>

  <returnType xmlns="urn:agent-management-data-structures">ALL</returnType>

  <recursive xmlns="urn:agent-management-data-structures">false</recursive>

  </ObjectSelector>

  </ArrayOfObjectSelector>

  </includeSelectionItemsTemp>

  using this query

  SELECT[JobSourceId]

  ,[IncludeSelectionItems].value('(/includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/specs/name)[1]','nvarchar(255)') as UUID

  FROM[WebCC].[dbo].[JobSource]

  WHEREcast([IncludeSelectionItems] as varchar(max)) like '%ORDBEH%'

  I get the record i want but no UUID

  /includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/*)[6]

  Gives me the start, but then i get the whole of "specs".

  ideally i would like 2 hints 🙂

  A quick solution for my current problem, and a link to some URL where i can learn more about how to query more complex XML

  grtz,

  Theo

• jasona.work

  SSC Guru

  Points: 50082

  More actions

  June 18, 2015 at 11:38 am

  #1806486

  I don't have the URLs I used to work out xQuery myself (recently,) but try this in your .value:

  ,[IncludeSelectionItems].value('(/includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/specs[@xmlns="urn:agent-management-data-structures"]/name)[1]','nvarchar(255)') as UUID

• Mikael Eriksson SE

  SSCommitted

  Points: 1706

  More actions

  June 26, 2015 at 2:40 am

  #1808331

  The nodes specs and name have a namespace. You can use * as wildcard character for a namespace or you can use WITH XMLNAMESPACES (Transact-SQL).

  .value('(/includeSelectionItemsTemp/ArrayOfObjectSelector/ObjectSelector/*:specs/*:name/text())[1]', 'uniqueidentifier')