Please try the following solution. What we are doing here is called shredding, i.e. converting XML into rectangular/relational format.
I am shooting from the hip because DDL and sample data population were not provided.
The provided XML is not well-formed due to missing root element, but SQL Server allows to handle XML fragments.
We are using XQuery and its .nodes()
and .value()
methods.
SQL, Method #1
-- DDL and sample data population, start
DECLARE @xml XML =
N'<DW>
<KS>
<KeyInfo Name="IlluSetting">
<KeyTypeValue>Text</KeyTypeValue>
<ValueString>yDipol90</ValueString>
</KeyInfo>
<KeyInfo Name="IlluSetting2">
<KeyTypeValue>Text</KeyTypeValue>
<ValueString>yDipol</ValueString>
</KeyInfo>
</KS>
<MDESC>Tx [mrad]</MDESC>
<MNUMBER>0.12102</MNUMBER>
</DW>
<DW>
<KS>
<KeyInfo Name="IlluSetting3">
<KeyTypeValue>Text</KeyTypeValue>
<ValueString>yDipol80</ValueString>
</KeyInfo>
</KS>
<MDESC>Ty [mrad]</MDESC>
<MNUMBER>0.12102</MNUMBER>
</DW>';
-- DDL and sample data population, end
SELECT c.value('KeyInfo[1]/@Name', 'VARCHAR(30)') AS name1
, c.value('(KeyInfo[1]/ValueString/text())[1]', 'VARCHAR(30)') AS ValueString1
, COALESCE(c.value('KeyInfo[2]/@Name', 'VARCHAR(30)'), '') AS name2
, COALESCE(c.value('(KeyInfo[2]/ValueString/text())[1]', 'VARCHAR(30)'), '') AS ValueString2
FROM @xml.nodes('/DW/KS') AS t(c);
Output
+--------------+--------------+--------------+--------------+
| name1 | ValueString1 | name2 | ValueString2 |
+--------------+--------------+--------------+--------------+
| IlluSetting | yDipol90 | IlluSetting2 | yDipol |
| IlluSetting3 | yDipol80 | | |
+--------------+--------------+--------------+--------------+
SQL, Method #2
DECLARE @CrLf CHAR(2) = CHAR(13) + CHAR(10)
, @tokenCounter INT
, @i INT = 1;
-- Calculate max number of tokens in the <KS>
SET @tokenCounter = (SELECT MAX(c.value('count(KeyInfo)', 'INT'))
FROM @xml.nodes('/DW/KS') AS t(c));
DECLARE @SQL NVARCHAR(MAX) = 'SELECT ';
WHILE @i <= @tokenCounter BEGIN
SET @SQL += IIF(@i>1,', ','') + 'COALESCE(c.value(''KeyInfo[' + CAST(@i AS VARCHAR(3)) + ']/@Name'', ''VARCHAR(30)''), '''') AS NAME' + CAST(@i AS VARCHAR(3)) + @CrLf
SET @SQL += ', COALESCE(c.value(''(KeyInfo[' + CAST(@i AS VARCHAR(3)) + ']/ValueString/text())[1]'', ''VARCHAR(30)''), '''') AS ValueString' + CAST(@i AS VARCHAR(3)) + @CrLf
SET @i += 1
END
SET @SQL += 'FROM @xml.nodes(''/DW/KS'') AS t(c);';
-- just to see it
PRINT @sql;
-- we are ready at this point
EXEC sp_executesql @stmt = @SQL, @params = N'@xml xml', @xml = @xml;
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…