如何查询CRM中营销列表视图的属性？查询语句相关问询

Hey there, let's break down how to grab those view attributes you're after in CRM. Your current query pulls SavedQuery records for Active Marketing Lists, but it misses the actual column (attribute) details that show up in the view—here's how to fix that:

Step 1: Retrieve the View's Column Definition XML #

Your initial query targets the right entity (SavedQuery stores system views), but you need to focus on the ColumnSetXml field. This is where the view's column configurations are saved as XML. Update your query to pull this critical data:

SELECT Name AS ViewName, ColumnSetXml
FROM SavedQuery
WHERE ReturnedTypeCode = 4300  -- 4300 is the entity code for Marketing List (List)
  AND StateCode = 0             -- Filter for active views only
  AND Name LIKE 'Active Marketing Lists%'

Step 2: Extract Attribute Logical Names from XML #

The ColumnSetXml will look like a simplified snippet below, with each <column> node defining an attribute in the view:

<columnset><column name="name" /><column name="listtype" /><column name="membertypecode" /><column name="createdon" /></columnset>

To pull these attribute names directly in SQL, use XML parsing functions to extract the name attribute from each <column> node:

SELECT
  sq.Name AS ViewName,
  col.value('@name', 'nvarchar(100)') AS AttributeLogicalName
FROM SavedQuery sq
CROSS APPLY sq.ColumnSetXml.nodes('/columnset/column') AS cols(col)
WHERE sq.ReturnedTypeCode = 4300
  AND sq.StateCode = 0
  AND sq.Name LIKE 'Active Marketing Lists%'

Step 3: Map Logical Names to UI Display Names #

The query above gives you backend logical names, but you want the friendly display names you see in the view (like "Listname", "Type", "Member Type"). Here's the direct mapping for the Marketing List (List) entity:

• name → List Name
• listtype → Type
• membertypecode → Member Type
• createdon → Created On
• modifiedon → Modified On

If you need to fetch display names programmatically instead of mapping manually, you can query the AttributeMetadata entity (via CRM Web API or Metadata Service), which stores all attribute details including user-facing labels.

Quick Checks #

• You nailed using ReturnedTypeCode = 4300—that's exactly the entity code for Marketing Lists.
• StateCode = 0 correctly filters for active views, matching your "Active Marketing Lists" target.

内容的提问来源于stack exchange，提问作者Raj