将XML数据输入到现有的sql表 [英] Input XML data to existing sql table
问题描述
您好,我想知道是否有人可以提供帮助。我很确定它是一个简单的解决方案,但我似乎无法让它工作。我有一个简单的xml文件,非常简单。
< ResultsDS >
< 结果 >
< Yesno_Vote > 否< / Yesno_Vote >
< PollPosition_1 > person1 < / PollPosition_1 >
< PollPosition_2 > person2 < / PollPosition_2 >
< PollPosition_3 > person3 < / PollPosition_3 >
< PollPosition_4 > person4 < / PollPosition_4 >
< PollPosition_5 > person5 < / PollPosition_5 >
< / Results >
< / ResultsDS >
i希望提取数据并将其放入现有sql表中的相关列中。
表有7列,xml文件中的6个命名元素和一个标识列。
我基本上喜欢< YesNo_Vote>的值。要插入到我的sql表的YesNo_Vote列中的元素。等等。
任何帮助都会非常感激。
提前感谢。
您可以编写一个可以执行此操作的存储过程。
DECLARE @ iDoc INT / * 存储指向XML文档的指针* /
DECLARE @ XML VARCHAR (MAX) / * 存储XML的内容* /
set @ XML = ' < ResultsDS>
<结果>
< Yesno_Vote>否< / Yesno_Vote>
< PollPosition_1> person1< / PollPosition_1>
< PollPosition_2> person2< / PollPosition_2>
< PollPosition_3> person3< / PollPosition_3>
< PollPosition_4> person4< / PollPosition_4>
< PollPosition_5> person5< / PollPosition_5>
< / Results>
< / ResultsDS>'
EXEC sp_xml_preparedocument @ iDoc OUTPUT , @ XML
INSERT INTO YourTable
SELECT *
FROM OPENXML ( @ iDoc ,' / ResultsDS / Results', 2 )
WITH (Yesno_Vote varchar ( 50 )' Yesno_Vote',
PollPosition_1 varchar ( 50 )' PollPo sition_1',
PollPosition_2 varchar ( 50 )' PollPosition_2',
PollPosition_3 varchar ( 50 )' PollPosition_3',
PollPosition_4 varchar ( 50 )' PollPosition_4',
PollPosition_5 varchar ( 50 )' PollPosition_5'
)
EXEC sp_xml_removedocument @ iDoc
我假设数据类型为varchar(50)
,根据你的表格改变它们的类型rties。
Hi there, I was wondering if anyone could help. Im pretty sure its a simple solution but I cant seem to get it working. I have a simple xml file, very simple.
<ResultsDS>
<Results>
<Yesno_Vote>NO</Yesno_Vote>
<PollPosition_1>person1</PollPosition_1>
<PollPosition_2>person2</PollPosition_2>
<PollPosition_3>person3</PollPosition_3>
<PollPosition_4>person4</PollPosition_4>
<PollPosition_5>person5</PollPosition_5>
</Results>
</ResultsDS>
i would like to extract the data and put it into relevent columns in my existing sql table.
the table has 7 columns, the 6 named elements in the xml file and an identity column.
and i would essentially like the value of the <YesNo_Vote> element to be inserted into the YesNo_Vote column of my sql table. and so on and so forth.
any help would be much appreciated.
thanks in advance.
You can write a Stored Procedure which can do this.
DECLARE @iDoc INT /* Stores a pointer to the XML document */ DECLARE @XML VARCHAR(MAX) /* Stores the content of the XML */ set @XML = '<ResultsDS> <Results> <Yesno_Vote>NO</Yesno_Vote> <PollPosition_1>person1</PollPosition_1> <PollPosition_2>person2</PollPosition_2> <PollPosition_3>person3</PollPosition_3> <PollPosition_4>person4</PollPosition_4> <PollPosition_5>person5</PollPosition_5> </Results> </ResultsDS>' EXEC sp_xml_preparedocument @iDoc OUTPUT, @XML INSERT INTO YourTable SELECT * FROM OPENXML(@iDoc,'/ResultsDS/Results',2) WITH (Yesno_Vote varchar(50) 'Yesno_Vote', PollPosition_1 varchar(50) 'PollPosition_1', PollPosition_2 varchar(50) 'PollPosition_2', PollPosition_3 varchar(50) 'PollPosition_3', PollPosition_4 varchar(50) 'PollPosition_4', PollPosition_5 varchar(50) 'PollPosition_5' ) EXEC sp_xml_removedocument @iDoc
I assumed the data types to bevarchar(50)
, change them according to your table''s properties.
这篇关于将XML数据输入到现有的sql表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!