covert the xml data to Table
Could any one help me to convert the below data to table format.
<people>
<person>
<name>John Doe</name>
<age>21</age>
</person>
<person>
<name>Jane Smith</name> 开发者_开发技巧
<age>24</age>
</person>
</people>
Thanks in advance
Here is how you can get the data from the XML. You can use the query as a source for the insert statement to a table.
declare @xml xml =
'<people>
<person>
<name>John Doe</name>
<age>21</age>
</person>
<person>
<name>Jane Smith</name>
<age>24</age>
</person>
</people>'
select T.X.value('name[1]', 'varchar(50)') as Name,
T.X.value('age[1]', 'int') as Age
from @xml.nodes('/people/person') as T(X)
Result:
Name Age
-------------------------------------------------- -----------
John Doe 21
Jane Smith 24
You can try XSLT to style your xml data. Save this astest.xml:
<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="test.xsl"?>
<people>
<person>
<name>John Doe</name>
<age>21</age>
</person>
<person>
<name>Jane Smith</name>
<age>24</age>
</person>
</people>
Save this into test.xsl:
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
<html>
<body>
<table border="1">
<tr bgcolor="#9acd32">
<th>name</th>
<th>age</th>
</tr>
<xsl:for-each select="people/person">
<tr>
<td><xsl:value-of select="name"/></td>
<td><xsl:value-of select="age"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
</xsl:stylesheet>
Then drag the xml into IE to see the result.
加载中,请稍侯......
精彩评论