我可以找到很多关于如何将某些类型的XML数据导入sql Server 2005的示例.但是我已经获得了以下格式的数据(重复“行”和“单元格”,而不是标记的名称等等：
<?xml version="1.0"?> <rows>
<row id='1'>
<cell id='category'>Simple</cell>
<cell id='query'>summary</cell>
<cell id='clientsfound'>6</cell>
<cell id='eligibleclients'>11</cell>
<cell id='percentage'>55</cell>
<cell id='days'>0</cell>
</row>
<row id='2'>
<cell id='category'>Complex</cell>
<cell id='query'>details</cell>
<cell id='clientsfound'>4</cell>
<cell id='eligibleclients'>6</cell>
<cell id='percentage'>67</cell>
<cell id='days'>5</cell>
</row>
...
</rows>
理想情况下,我想将其加载到表中,例如：
CREATE TABLE [dbo].[QueryResults](
[UserString] [varchar](50) NULL,[ImportStamp] [timestamp] NULL,[RowID] [int] NULL,[Category] [nchar](10) NULL,[Query] [nchar](10) NULL,[ClientsFound] [int] NULL,[EligibleClients] [int] NULL,[Percentage] [int] NULL,[Days] [int] NULL
)
有人能为我提供一个示例或指向在线教程吗？
xml应该“”不是’内部,不是？
无论如何,您可以本机解析XML数据类型.
由于内存使用量的开销,sp_xml_preparedocument非常危险.
DECLARE @foo XML;
SET @foo = N'<?xml version="1.0"?>
<rows>
<row id="1">
<cell id="category">Simple</cell>
<cell id="query">summary</cell>
<cell id="clientsfound">6</cell>
<cell id="eligibleclients">11</cell>
<cell id="percentage">55</cell>
<cell id="days">0</cell>
</row>
<row id="2">
<cell id="category">Complex</cell>
<cell id="query">details</cell>
<cell id="clientsfound">4</cell>
<cell id="eligibleclients">6</cell>
<cell id="percentage">67</cell>
<cell id="days">5</cell>
</row>
</rows>';
SELECT
x.item.value('@id','int') AS RowID,y.item.value('(./cell[@id="category"])[1]','nchar(10)') AS category,y.item.value('(./cell[@id="query"])[1]','nchar(10)') AS query,y.item.value('(./cell[@id="clientsfound"])[1]','int') AS clientsfound,y.item.value('(./cell[@id="eligibleclients"])[1]','int') AS eligibleclients,y.item.value('(./cell[@id="percentage"])[1]','int') AS percentage,y.item.value('(./cell[@id="days"])[1]','int') AS days
FROM
@foo.nodes('/rows/row') x(item)
CROSS APPLY
x.item.nodes('.') AS y(item)
