Example: Retrieve Dependent Picklist Information

Retrieving Picklist Information

To retrieve all dependent Picklist information for the Lead Object:

Create an empty Input table:

 CREATE TABLE MD_LeadPicklists ([Name] [nvarchar](255) NULL,

 [Member] [nvarchar](255) NULL,

 [MetadataXML] [xml] NULL,

 [CreatedByWildcard] [bit] NULL,

 [CreatedByList] [bit] NULL,

 [Error] [nvarchar](255) NULL,

 [ID] [nchar](18) NULL
  
 )

Populate the Input table. Insert a single row into the table with the Name column of CustomObject and the Member column of Lead:
```
  INSERT INTO MD_LeadPicklists (Name,Member) Values ( 'CustomObject', 'Lead')
```

Run SF_Metadata to retrieve the information.

 EXEC SF_Metadata 'Retrieve', 'Salesforce', 'MD_LeadPicklists'

Run the following query against the table to generate the results:

  -- Query to select dependent picklists
  ;WITH XMLNAMESPACES(DEFAULT 'http://soap.sforce.com/2006/04/metadata') SELECT Member
  ,fn.c.value('(../../../fullName)[1]','nvarchar(50)') as FieldName
  ,fn.c.value('(../../controllingField)[1]','nvarchar(50)') as ControllingFieldName
  ,fn.c.value('(../valueName)[1]','nvarchar(50)') as PicklistValue ,
  fn.c.value('(.)[1]','nvarchar(50)') as ControllingPicklistValue
  FROM MD_LeadPicklists
  cross apply metadataxml.nodes
  ('/CustomObject/fields/valueSet/valueSettings/controllingFieldValue') as fn(c)

Results

   Member FieldName ControllingFieldNa PicklistValue ControllingPicklistValue

   Lead ProductInterest__c Industry GC1000 series Agriculture

   Lead ProductInterest__c Industry GC1000 series Apparel
   
   Lead ProductInterest__c Industry GC1000 series Banking

   Lead ProductInterest__c Industry GC1000 series Biotechnology

   Lead ProductInterest__c Industry GC1000 series Construction

   Lead ProductInterest__c Industry GC1000 series Education

   Lead ProductInterest__c Industry GC5000 series Biotechnology

   Lead ProductInterest__c Industry GC5000 series Chemicals

   Lead ProductInterest__c Industry GC5000 series Construction

   Lead ProductInterest__c Industry GC5000 series Electronics