xmlsql_xml与sql的区别 - 程序员百科

A. mybatis xml怎么打印sql

想要打印工程项目中所有sql并判断是否带有where条件就比较明晰了，直接上代码：
@Resource
private SqlSessionFactory sqlSessionFactory;
@Test
public void test_check() {
Configuration configuration = sqlSessionFactory.getConfiguration();
System.out.println("#sql.size#" + configuration.getMappedStatements().size());
Set<String> errors = Sets.newHashSet();
int i = 1;
for (Object obj : configuration.getMappedStatements()) {
if (obj instanceof MappedStatement) {
MappedStatement mappedStatement = (MappedStatement) obj;
String sql = mappedStatement.getSqlSource().getBoundSql(new SqlParamMap()).getSql();
sql = sql.replaceAll("\n", "");
sql = sql.replaceAll("\\s+", " ");
System.out.println(String.format("#sql,#%02d #%s #%s", i++, mappedStatement.getSqlCommandType(), sql));
if (!sql.toLowerCase().startsWith("insert") && !sql.toLowerCase().startsWith("select")
&& !sql.toLowerCase().startsWith("replace")) {
if (!sql.toLowerCase().contains("where")) {
errors.add(sql);
}
}
}
}
System.err.println("#error#" + errors.size());
for (String errorSql : errors) {
System.err.println(errorSql);
}
}

// 这里为了方便生成sql时，解析入参对象的
public static class SqlParamMap extends AbstractMap<String, Object> implements Map<String, Object> {
@Override
public Set<Entry<String, Object>> entrySet() {
return Collections.emptySet();
}
@Override
public Object get(Object key) {
return new Object[] {1, 2};
}
}

B. SQL如何动态的解析XML

(freeware)http://www.CodeHighlighter.com/-->DECLARE@ItemMessageXML
DECLARE@ItemTableTABLE(ItemNumberINTPRIMARYKEY,ItemDescriptionNVARCHAR(300))
SET@ItemMessage=N'<ItemList>
<Item>
<ItemNumber>1</ItemNumber>
<ItemDescription>XBox360,超值</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>WindowsPhone7,快来尝鲜吧</ItemDescription>
</Item>
</ItemList>'
INSERTINTO@ItemTable
(
ItemNumber,
ItemDescription
)
SELECTT.c.value('(ItemNumber/text())[1]','INT'),
T.c.value('(ItemDescription/text())[1]','NVARCHAR(300)')
[email protected]('/ItemList/Item')AST(c)
SELECTItemNumber,
ItemDescription
FROM@ItemTable

(freeware)http://www.CodeHighlighter.com/-->DECLARE@ItemMessageXML
DECLARE@ItemTableTABLE(ItemNumberINTPRIMARYKEY,ItemDescriptionNVARCHAR(300))
SET@ItemMessage=N'<ItemListxmlns="http://cd.love.com/SOA">
<Item>
<ItemNumber>1</ItemNumber>
<ItemDescription>XBox360,超值</ItemDescription>
</Item>
<Item>
<ItemNumber>2</ItemNumber>
<ItemDescription>WindowsPhone7,快来尝鲜吧</ItemDescription>
</Item>
</ItemList>'
;WITHXMLNAMESPACES(DEFAULT'http://cd.love.com/SOA')
INSERTINTO@ItemTable
(
ItemNumber,
ItemDescription
)
SELECTT.c.value('(ItemNumber/text())[1]','INT'),
T.c.value('(ItemDescription/text())[1]','NVARCHAR(300)')
[email protected]('/ItemList/Item')AST(c)
SELECTItemNumber,
ItemDescription
FROM@ItemTable

C. 如何将xml文件转换成sql的表

select * from 表名 for xml 以下是详细介绍： FOR 子句 FOR 子句用于指定 BROWSE 或 XML 选项（BROWSE 和 XML 是不相关的选项）。语法 [ FOR { BROWSE | XML { RAW | AUTO | EXPLICIT } [ , XMLDATA ] [ , ELEMENTS ] [ , BINARY BASE64 ] } ]...

D. 在xml文件中插入sql语句

<%@LANGUAGE="VBSCRIPT" CODEPAGE="65001"%>
<%
Response.ContentType="application/xml"
Response.Charset="utf-8"
response.cachecontrol="no-cache"
response.addHeader "pragma","no-cache"
response.expires=-1
response.expiresAbsolute=now-1

db="file.mdb" '数据库路径，相对路径
set conn = server.CreateObject("adodb.connection")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(db)

Response.Write("<?xml version=""1.0"" encoding=""utf-8""?>")
set rs=Server.CreateObject("adodb.recordset")
rs.open "SELECT * FROM [file]",conn,1,1
if rs.Eof and rs.Bof then
Response.Write("<vcaster>")
Response.Write ("<item item_url=""no file"" item_title=""no file"">")
Response.Write("</item>")
Response.Write ("</vcaster>")
else
Response.Write("<vcaster>")
do while Not rs.Eof

Response.Write ("<item item_url="""&rs("fileurl")&""" item_title="""&rs("name")&""">")
Response.Write("</item>")

if rs.Eof then
Exit do
End if
rs.MoveNext
Loop
Response.Write ("</vcaster>")
End if
rs.Close
Set rs=Nothing
conn.Close
Set conn=Nothing
%>

E. xml文件如何写入sql数据库

sqlserver2005分解并导入xml文件[@more@]
1.
一次性导入：
declare
@idoc
int;
declare
@doc
xml;
select
@doc=bulkcolumn
from
openrowset(bulk
n'e:mstarinstrycodes.xml',
single_blob)
as
x
exec
sp_xml_preparedocument
@idoc
output,
@doc
select
*
into
tmp_tab
from
openxml
(@idoc,
'/root/record'/',2)
with
(
instrycode
varchar(10)
,instryglobalid
varchar(10)
,instryname
varchar(100)
,sectorcode
varchar(10)
,sectorglobalid
varchar(10)
,sectorname
varchar(100)
,supersectorcode
varchar(10)
,supersectorname
varchar(100)
,groupcode
varchar(10)
,groupname
varchar(100)
,countryid
varchar(3)
)
exec
sp_xml_removedocument
@idoc
select
*
from
tmp_tab
2.
先导入到表中varchar(max)列，然后再用openxml解析，读出。
--
使用single_clob参数，tmp_raw中字段为varcahr(max)类型
select
*
into
tmp_raw
from
openrowset(bulk
n'e:mstarinstrycodes.xml',
single_clob)
as
x
declare
@idoc
int;
declare
@doc
xml;
select
@doc
=
bulkcolumn
from
tmp_raw
exec
sp_xml_preparedocument
@idoc
output,
@doc
select
top
10
*
from
openxml
(@idoc,
'/root/record',
1)
with
(
instrycode
varchar(10)
,instryglobalid
varchar(10)
,instryname
varchar(100)
,sectorcode
varchar(10)
,sectorglobalid
varchar(10)
,sectorname
varchar(100)
,supersectorcode
varchar(10)
,supersectorname
varchar(100)
,groupcode
varchar(10)
,groupname
varchar(100)
,countryid
varchar(3)
)
exec
sp_xml_removedocument
@idoc

F. xml文件导入sql数据库

SQLServer2005分解并导入xml文件[@more@]
1. 一次性导入：
DECLARE @idoc int;
DECLARE @doc xml;
SELECT @doc=BulkColumn FROM OPENROWSET(BULK N'E:MStarInstryCodes.xml', SINGLE_BLOB) AS x
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

SELECT * into tmp_tab FROM OPENXML (@idoc, '/Root/Record'/',2)
WITH
(
InstryCode varchar(10)
,InstryGlobalId varchar(10)
,InstryName varchar(100)
,SectorCode varchar(10)
,SectorGlobalId varchar(10)
,SectorName varchar(100)
,SuperSectorCode varchar(10)
,SuperSectorName varchar(100)
,GroupCode varchar(10)
,GroupName varchar(100)
,CountryId varchar(3)
)
EXEC sp_xml_removedocument @idoc
select * from tmp_tab
2. 先导入到表中varchar(MAX)列，然后再用OPENXML解析，读出。
-- 使用SINGLE_CLOB参数，tmp_raw中字段为varcahr(MAX)类型
SELECT * into tmp_raw FROM OPENROWSET(BULK N'E:MStarInstryCodes.xml', SINGLE_CLOB) AS x
DECLARE @idoc int;
DECLARE @doc xml;
select @doc = BulkColumn from tmp_raw
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT top 10 * FROM OPENXML (@idoc, '/Root/Record', 1)
WITH
(
InstryCode varchar(10)
,InstryGlobalId varchar(10)
,InstryName varchar(100)
,SectorCode varchar(10)
,SectorGlobalId varchar(10)
,SectorName varchar(100)
,SuperSectorCode varchar(10)
,SuperSectorName varchar(100)
,GroupCode varchar(10)
,GroupName varchar(100)
,CountryId varchar(3)
)
EXEC sp_xml_removedocument @idoc

G. SQL 中怎么修改XML的值

不知道你使用的是什么语言，一般就是获取到数据库中的字段然后复制到对应的属性或者文本中就行可以了修改属性，和创建属性都可以这样做得，不知道是不是你想要得 XmlElement Category = doc.CreateElement("Category");//Category节点 CategoryId = ds.Tables[0].Rows[i]["categroyID"].ToString();
curriculumName = ds.Tables[0].Rows[i]["categroy"].ToString();
imgUrl = ds.Tables[0].Rows[i]["imgurl"].ToString();

H. sql语法里面的 xml 的截取

declare @xml xml
declare @str varchar(100)
set @xml=(SELECT CAST('<A>ABC</A>' AS XML) ) --更改为你的SQL语句即可
set @str= convert(varchar(100), @xml )
set @str=substring(@str,charindex('>',@str)+1,charindex('/',@str)-charindex('>',@str)-2)
print @str

I. xml与sql的区别

xml是为了各种平台间共享数据的标准语言，通过标签节点来组织数据，具有平台无关，且结构好理解；
sql是用来在数据库中进行查、删、改、更新的语句，专门为数据库设计的，且每种数据库的sql语句有一些区别。

xmlsql

与xmlsql相关的内容