用到的工具:
- SqlServer
- java
- mybatis
第一步:创建function,用于获取xml中的数据
CREATE function create_table (@str xml) returns @tb table(sourceId varchar(20)) as begin insert into @tb SELECT v.value('@sourceId[1]','VARCHAR(20)') AS sourceId FROM @str.nodes('/RSSsources/rssSource') x(v) return end
第二步:用存储过程调用上面的函数创建表格
CREATE PROCEDURE T_PRO(@doc xml) AS BEGIN CREATE TABLE tbl(sourceId varchar(20)) INSERT INTO tbl select b.sourceId from create_table(@doc) b END
第三步:在数据库中调用存储过程
DECLARE @doc xml SET @doc = '<RSSsources><rssSource sourceId="1"/><rssSource sourceId="2"/><rssSource sourceId="3"/><rssSource sourceId="4"/></RSSsources>' EXEC T_PRO @doc
第四步:mybatis调用存储过程
<select id="createTable" statementType="CALLABLE" resultType="string">
{call T_PRO(#{xml})}
</select>
第五步:java程序中调用存储过程
SqlSessionFactory factory = GetSqlSessionFactory.getInstance(); SqlSession sqlSession = factory.openSession(); NewsInfoMapper mapper = sqlSession.getMapper(NewsInfoMapper.class); String sourceXml = "<RSSsources><rssSource sourceId=\"1\"/><rssSource sourceId=\"2\"/><rssSource sourceId=\"3\"/><rssSource sourceId="4"/></RSSsources>"; mapper.createTable(sourceXml); sqlSession.commit(); sqlSession.close();