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相關的內容