将XML导入SQL Server
发布时间:2020-12-16 07:58:21 所属栏目:百科 来源:网络整理
导读:我可以找到很多关于如何将某些类型的XML数据导入SQL Server 2005的示例.但是我已经获得了以下格式的数据(重复“行”和“单元格”,而不是标记的名称等等: ?xml version="1.0"? rows row id='1' cell id='category'Simple/cell cell id='query'summary/cell c
我可以找到很多关于如何将某些类型的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数据类型. 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) (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
相关内容