如何将SSAS表格模型的XMLA文件转换为XML格式？

Hey there, let's work through converting your SSAS Tabular Model's XMLA file into a standard XML format that your ER diagram tool can handle. XMLA is technically XML, but it's packed with SSAS-specific metadata that most ER tools don't recognize—so we need to extract or transform the core bits your tool cares about. Here are a few practical approaches:

Since XMLA is just specialized XML, you can directly pull out the ER-relevant sections using a text editor like VS Code or Notepad++:

• Open your XMLA file and locate the <Model> node. Inside it, you'll find two key sections:
  • <Tables>: Contains all your model's tables, with nested <Columns> nodes for each table's fields (ignore <Partitions> unless your ER tool needs them)
  • <Relationships>: Defines all table-to-table associations
• Copy these two sections, then wrap them in a simple XML root node. Make sure to include the SSAS namespace to avoid parsing errors:

<ERModel xmlns="http://schemas.microsoft.com/analysisservices/2013/engine/1000">
  <Tables>
    <!-- Paste your XMLA <Tables> content here -->
  </Tables>
  <Relationships>
    <!-- Paste your XMLA <Relationships> content here -->
  </Relationships>
</ERModel>

Save this as a new .xml file—many ER tools will recognize this stripped-down structure.

If your model is too big to handle manually, leverage SSMS and PowerShell to generate a cleaner XML:

1. Open SQL Server Management Studio (SSMS), connect to your SSAS Tabular instance
2. Right-click your model database > Script Database as > CREATE To > New Query Editor Window
3. By default, this generates XMLA, but you can switch to TMSL (a more concise JSON-based format) via the SSMS toolbar's "Script as" dropdown
4. Save the TMSL as model.tmsl, then run this PowerShell command to convert it to XML:

$tmslContent = Get-Content -Path "model.tmsl" -Raw | ConvertFrom-Json
$xmlContent = $tmslContent | ConvertTo-Xml -NoTypeInformation
$xmlContent.Save("model.xml")

The resulting XML will be far simpler than raw XMLA, with only the core model structure your ER tool needs.

If your ER tool expects a specific XML schema, write a quick script to extract exactly what you need. This example pulls table names, columns, data types, and relationships:

# Load the XMLA file
$xmla = [xml](Get-Content -Path "your_model.xmla" -Raw)
# Set up the SSAS namespace
$ns = New-Object System.Xml.XmlNamespaceManager($xmla.NameTable)
$ns.AddNamespace("ssas", "http://schemas.microsoft.com/analysisservices/2013/engine/1000")

# Create a new XML document for the ER model
$erXml = New-Object System.Xml.XmlDocument
$root = $erXml.CreateElement("ERDiagramModel")
$erXml.AppendChild($root)

# Extract tables and columns
$tables = $xmla.SelectNodes("//ssas:Table", $ns)
foreach ($table in $tables) {
    $tableNode = $erXml.CreateElement("Table")
    $tableNode.SetAttribute("Name", $table.Name)
    
    $columns = $table.SelectNodes(".//ssas:Column", $ns)
    foreach ($column in $columns) {
        $colNode = $erXml.CreateElement("Column")
        $colNode.SetAttribute("Name", $column.Name)
        $colNode.SetAttribute("DataType", $column.DataType)
        $tableNode.AppendChild($colNode)
    }
    $root.AppendChild($tableNode)
}

# Extract relationships
$relationships = $xmla.SelectNodes("//ssas:Relationship", $ns)
foreach ($rel in $relationships) {
    $relNode = $erXml.CreateElement("Relationship")
    $relNode.SetAttribute("FromTable", $rel.FromTable)
    $relNode.SetAttribute("FromColumn", $rel.FromColumn)
    $relNode.SetAttribute("ToTable", $rel.ToTable)
    $relNode.SetAttribute("ToColumn", $rel.ToColumn)
    $root.AppendChild($relNode)
}

# Save the final XML
$erXml.Save("er_model.xml")

You can tweak the node names/attributes to match your ER tool's required schema—just check the tool's documentation for specifics.

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