SQL Server 2008 - Selecting multiple rows from OPENXML statement
I have an XML file and I open that in SQL Server using OPENXML and then read the values in the XML file and insert them into the table. Assume that the XML structure is like this
<Student>
<name&开发者_JS百科gt;XYZ</name>
<id>123</id>
<fathersname>XYS</fathersname>
<fathersid>3489</fathersid>
</Student>".
Now I need to add this as two different rows and the DB should look like this
Name
ID
XYZ 123
XYS 3489
How can i read from thisXML
and insert as two different rows using a single OPENXML
statement?CREATE TABLE dbo.Person(ID int, [Name] varchar(50))
DECLARE @docHandle int
DECLARE @xmlDocument XML
SET @xmlDocument = N'<ROOT>
<Student>
<name>XYZ</name>
<id>123</id>
<fathersname>XYS</fathersname>
<fathersid>3489</fathersid>
</Student>
<Student>
<name>ABC</name>
<id>456</id>
<fathersname>DEF</fathersname>
<fathersid>7859</fathersid>
</Student>
</ROOT>'
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument
-- student's data first
INSERT INTO dbo.Person
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Student',2)
WITH (id int, name varchar(50))
-- now insert father's data
INSERT INTO dbo.Person
SELECT *
FROM OPENXML(@docHandle, N'/ROOT/Student',2)
WITH (fathersid int, fathersname varchar(50))
SELECT * FROM dbo.Person
EXEC sp_xml_removedocument @docHandle
DROP TABLE dbo.Person
To open from a file:
declare @xmlDocument XML
SET @xmlDocument=(SELECT * FROM OPENROWSET(
BULK 'c:\Temp\Student.xml',
SINGLE_BLOB) AS x)
UPDATE:
Sorry, did not see that you are trying to split <father>
into a different row, I have simply added one more INSERT for that. If you need more control over the loading process, you can always consider setting up an ETL job in SSIS.
UPDATE 2
Well here is a creative way with one insert only, but two selects -- not sure about the performance at all. Again, we are splitting one record into two rows.
INSERT INTO dbo.Person
SELECT
x.e.value('id[1]', 'int') AS "id"
,x.e.value('name[1]', 'varchar(10)') AS "Name"
FROM @xmlDocument.nodes('/ROOT/Student') AS x(e)
UNION
SELECT
x.e.value('fathersid[1]', 'int') AS "id"
,x.e.value('fathersname[1]', 'varchar(10)') AS "Name"
FROM @xmlDocument.nodes('/ROOT/Student') AS x(e);
精彩评论