Sql 数据导出到XMl 与 从XML导入到数据库
1、导出到XMl select * from Brand for xml auto ,root('Brands')
<Brands>
<Brand BrandID="E584596D-4D66-4F2F-B6F7-71C3BEB4CA21" Name="inganico" />
<Brand BrandID="19B04451-DDC4-4CDF-BE30-CB4E703B27DA" Name="安付达" />
<Brand BrandID="3C6C8E12-7C4A-4F19-B491-4C0A64A48303" Name="安智" />
<Brand BrandID="BF6C361A-8993-4660-A89D-EB32CCC9CE49" Name="百富" />
<Brand BrandID="8E7FE420-3AE3-4017-80AB-B53CA29C80CA" Name="海博通" />
<Brand BrandID="505C5565-08C5-4EF5-9316-55CA76C1E9F3" Name="惠尔丰" />
<Brand BrandID="E5BA2A72-B1D1-457A-9AFD-A9D9B336E7C0" Name="利普门" />
<Brand BrandID="1982A195-5263-45CC-B872-96F3C145FCCD" Name="联迪" />
<Brand BrandID="E460CDA6-4A83-4C62-B049-3B980516AD79" Name="瑞柏" />
<Brand BrandID="06BACF99-BB7E-447C-B021-CD8C3FFAE85A" Name="萨基姆" />
<Brand BrandID="165510D9-342D-4402-882D-0A00DBFDAAE3" Name="新大陆" />
<Brand BrandID="2FA88654-2311-4C51-85B1-A39077C34C6D" Name="新国都" />
</Brands>
2、将上边XML内容导回 数据库
CreatePROCEDURE [dbo].[P_BrandSynchronization]
@xmlDocument NVARCHAR (MAX)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @docHandle int
set @xmlDocument='<Brands>'+@xmlDocument+'</Brands>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
declare @BrandID uniqueidentifier
declare @Name varchar(16) , @message varchar(500)
declare mycursor cursor for (SELECT * from OPENXML (@docHandle,'/Brands/Brand',1)
WITH (
BrandID uniqueidentifier,
Name varchar(16)
))
open mycursor
FETCH NEXT from mycursor INTO @BrandID,@Name
while(@@FETCH_S
相关文档：
--备份事务日志 (自上一次备份以来，至当前的已完成的事务的日志)
backup log MyDBName to disk='F:\dbbak\GamePoint\MyDBName_log_2010052709.bak';
--截断日志(截断掉当前的已完成的事务的日志), SQL Server 2005, 2008没有这个选项.
backup log MyDBName with no_Log;
--收缩日志(至1M,� ......
/*
--2010-05-27(东升)
SQL SERVER 2008 新数据库类型hierarchyid
SQL SERVER 2008引入了新的hierarchyid数据类型,可以用它来做本地存储并且在树层次
结构中管理其位置.只用这个函数能简洁地表示层次结构中的位置.该函数提供的一些 ......
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="fw_student">
<resultMap class="com.sias.Student" id="student">
<result column="name" property="na ......
declare @xml xml
set @xml = '<root/>'
select @xml
declare @value varchar(10)
set @value = 'val1'
set @xml.modify('insert <item value="{sql:variable("@value")}" /> into (/root)[1]')
select @xml
set @value = 'val2'
set @xml.modify('replace value of (/root/item/@value)[1] with "val2 ......
准备将一个excel表导入SQL Server2005中发生了下图的错误：
重启SQL Server2005还是出现上图的错误，解决方法（如下图）：
在SQL Server Configuration Manager中将SSIS即SQL Server Integration Services的属性中的内置账户改为“本地系统”，重启服务即可导入excel了。 ......
