Need to Unpivot XML Data

I am having an XML Data like

<Results>
  <Project>
    <ID>111111</ID>
    <Name>Test Project</Name>
    <Abstract>This is a Test Project</Abstract>
    <ShortName>Sample Project</ShortName>
    <Language>ENG开发者_高级运维</Language>
  </Project>
</Results>

need to convert each element into rows as follows

1st row: 111111,ENG,ID,111111
2nd row: 111111,ENG,Name,Test Project
3rd row: 111111,ENG,abstract, This is a Test Project
4th row: 111111,ENG,shortName, Sample Project

How to do it?

Are you able to use XSLT to transform the XML? If so, something like the XSLT below should do you well.

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="text" encoding="utf-8"/>
<xsl:strip-space elements="*"/> 

<xsl:variable name='newline'><xsl:text>
</xsl:text></xsl:variable>

<xsl:template match="/Results/Project">
    <xsl:variable name="root" select="." />
    <xsl:for-each select="*">
        <xsl:value-of select="$root/ID/text()"/>,<xsl:value-of select="$root/Language/text()"/>,<xsl:value-of select="name()"/>,<xsl:value-of select="text()"/><xsl:value-of select="$newline"/>
    </xsl:for-each>
</xsl:template>

</xsl:stylesheet> 

This is a bit late answer. But here is a solution for SQL Server:

declare @x xml =
'<Results>
    <Project>
        <ID>111111</ID>
        <Name>Test Project</Name>
        <Abstract>This is a Test Project</Abstract>
        <ShortName>Sample Project</ShortName>
        <Language>ENG</Language>
    </Project>
</Results>'

select x.x.value('ID[1]/text()[1]', 'varchar(100)')
    + ','
    + x.x.value('Language[1]/text()[1]', 'varchar(100)')
    + ','
    + y.y.value('local-name(.)', 'varchar(100)')
    + ','
    + y.y.value('text()[1]', 'varchar(100)')
from @x.nodes('/Results[1]/Project[1]')x(x)
cross apply x.x.nodes('node()[not(local-name()="Language")]')y(y)