在SQL Server中将XML解析为键值对：解析指定系统配置XML

Got it, let's walk through how to parse this Dell system configuration XML into clean key-value pairs in SQL Server. I'll cover two practical approaches—one using the older but straightforward OPENXML method, and another with modern XQuery that handles nested components seamlessly.

This method works great if you want explicit control over traversing nested components. Here's how to do it:

First, we'll load the XML into a variable, then use SQL Server's XML stored procedures to parse it. We'll extract both the parent component's FQDD (for context) and each attribute's name/value.

DECLARE @xml XML = N'<SystemConfiguration Model="PowerEdge R740xd" ServiceTag="Test" TimeStamp="Wed Feb 14"> 
<Component FQDD="iDRAC.Embedded.1"> 
<Attribute Name="Info.1#Product">Integrated Dell Remote Access Controller</Attribute> 
<Attribute Name="IPMILan.1#Enable">Disabled</Attribute> 
<Attribute Name="IPMILan.1#PrivLimit">Administrator</Attribute> 
<Attribute Name="IPMILan.1#EncryptionKey">0</Attribute> 
<!-- <Attribute Name="AutoBackup.1#IPAddress"></Attribute> --> 
<!-- <Attribute Name="AutoBackup.1#Domain"></Attribute> --> 
</Component> 
<Component FQDD="RAID.Integrated.1-1"> 
<Attribute Name="RAIDresetConfig">True</Attribute> 
<Attribute Name="RAIDforeignConfig">Clear</Attribute> 
<Attribute Name="RAIDrekey">False</Attribute> 
<Attribute Name="EncryptionMode">None</Attribute> 
<Component FQDD="Disk.Virtual.8:RAID.Integrated.1-1"> 
<Attribute Name="RAIDaction">Create</Attribute> 
<Attribute Name="LockStatus">Unlocked</Attribute> 
<Attribute Name="RAIDinitOperation">None</Attribute> 
<Attribute Name="DiskCachePolicy">Default</Attribute> 
<Attribute Name="RAIDdefaultWritePolicy">WriteBack</Attribute> 
<Attribute Name="RAIDdefaultReadPolicy">ReadAhead</Attribute> 
<Attribute Name="Name">Virtual Disk 8</Attribute> 
<Attribute Name="Size">0</Attribute> 
<Attribute Name="StripeSize">0</Attribute> 
<Attribute Name="SpanDepth">1</Attribute> 
<Attribute Name="SpanLength">1</Attribute> 
<Attribute Name="RAIDTypes">RAID 0</Attribute> 
<Attribute Name="IncludedPhysicalDiskID">Disk.Bay.7:Enclosure...</Attribute></Component></Component></SystemConfiguration>';

DECLARE @hdoc INT;
-- Initialize the XML document handle
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml;

-- Extract attributes from top-level components
SELECT
    ComponentFQDD = [@FQDD],
    AttributeKey = [@Name],
    AttributeValue = [text()]
FROM OPENXML(@hdoc, '/SystemConfiguration/Component/Attribute', 2)
UNION ALL
-- Extract attributes from nested child components
SELECT
    ComponentFQDD = [@FQDD],
    AttributeKey = [@Name],
    AttributeValue = [text()]
FROM OPENXML(@hdoc, '/SystemConfiguration/Component/Component/Attribute', 2);

-- Clean up the document handle to avoid memory leaks
EXEC sp_xml_removedocument @hdoc;

说明： #

• We use UNION ALL to combine results from top-level components (like iDRAC) and nested components (like the virtual disk under RAID).
• The 2 in OPENXML specifies that we want to map attributes to columns directly.
• Commented-out <Attribute> nodes are automatically ignored since they aren't part of the actual XML structure.

If you prefer a more concise, modern approach that handles any level of nesting without extra UNION clauses, XQuery is the way to go. It directly targets all <Attribute> nodes regardless of their position in the XML tree:

DECLARE @xml XML = N'<SystemConfiguration Model="PowerEdge R740xd" ServiceTag="Test" TimeStamp="Wed Feb 14"> 
<Component FQDD="iDRAC.Embedded.1"> 
<Attribute Name="Info.1#Product">Integrated Dell Remote Access Controller</Attribute> 
<Attribute Name="IPMILan.1#Enable">Disabled</Attribute> 
<Attribute Name="IPMILan.1#PrivLimit">Administrator</Attribute> 
<Attribute Name="IPMILan.1#EncryptionKey">0</Attribute> 
<!-- <Attribute Name="AutoBackup.1#IPAddress"></Attribute> --> 
<!-- <Attribute Name="AutoBackup.1#Domain"></Attribute> --> 
</Component> 
<Component FQDD="RAID.Integrated.1-1"> 
<Attribute Name="RAIDresetConfig">True</Attribute> 
<Attribute Name="RAIDforeignConfig">Clear</Attribute> 
<Attribute Name="RAIDrekey">False</Attribute> 
<Attribute Name="EncryptionMode">None</Attribute> 
<Component FQDD="Disk.Virtual.8:RAID.Integrated.1-1"> 
<Attribute Name="RAIDaction">Create</Attribute> 
<Attribute Name="LockStatus">Unlocked</Attribute> 
<Attribute Name="RAIDinitOperation">None</Attribute> 
<Attribute Name="DiskCachePolicy">Default</Attribute> 
<Attribute Name="RAIDdefaultWritePolicy">WriteBack</Attribute> 
<Attribute Name="RAIDdefaultReadPolicy">ReadAhead</Attribute> 
<Attribute Name="Name">Virtual Disk 8</Attribute> 
<Attribute Name="Size">0</Attribute> 
<Attribute Name="StripeSize">0</Attribute> 
<Attribute Name="SpanDepth">1</Attribute> 
<Attribute Name="SpanLength">1</Attribute> 
<Attribute Name="RAIDTypes">RAID 0</Attribute> 
<Attribute Name="IncludedPhysicalDiskID">Disk.Bay.7:Enclosure...</Attribute></Component></Component></SystemConfiguration>';

SELECT
    ComponentFQDD = attr.value('../@FQDD', 'VARCHAR(100)'),
    AttributeKey = attr.value('@Name', 'VARCHAR(100)'),
    AttributeValue = attr.value('.', 'VARCHAR(200)')
FROM @xml.nodes('//Attribute') AS t(attr);

说明： #

• The //Attribute XPath expression selects every <Attribute> node in the XML, no matter how deeply nested it is.
• attr.value('../@FQDD', ...) grabs the parent <Component>'s FQDD to give context to each attribute.
• This method is cleaner and less error-prone for XML with variable nesting levels.

If your XML is stored in a file instead of a variable, you can load it directly using OPENROWSET:

DECLARE @xml XML;
SET @xml = (SELECT * FROM OPENROWSET(BULK 'C:\Path\To\Your\SystemConfig.xml', SINGLE_BLOB) AS xmlData);

-- Then run either the OPENXML or XQuery code above using @xml

Just make sure the SQL Server service account has read access to the file path.

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