More SQL XML, This Time Just Using SQL

Introduction

On the last few articles, I have been told there is another way twice so this has led to two more tips, which I do not mind as I am learning the Game and Craft of Development.

Background

I am a junior developer learning the ropes in a .NET environment, we are to get a new system that is fully .NET that handles XML heavily.

Using the Code

This is a very basic SQL script to convert some XML to a SQL Table.

XML

Below is part of the XML file used in the SQL below.

<ROOT>
  <Players>
    <squadnum>13</squadnum>
    <LName>Forster</LName>
    <Fname>Fraser</Fname>
    <position>G</position>
    <team>Southampton</team>
  </Players>
 // more XML Elements

</Root>

Creating the XML Variables with SQL

Below, I am using some of the basic XML tools that come with SQL Server. All I am doing below is a BLOB object and selecting it as Row. It is one massive piece of XML in one row, well one Cell, not pretty or possible that useful, but as in life beauty is in the eye of the beholder!

//make variable XML for the main doc and using open XML
Declare @xml xml 
declare @xml_handle int
// make the xml available 
set  @xml = (select * from openrowset_
(bulk 'C:\Users\colem2\Documents\players1.xml', single_blob)as x) 
// select the xml as one whole row
select @xml

Table to Receive the XML

I then create a table to receive the XML.

create table #bundle
(
num int,
ln varchar(24),
fn varchar(24),
pos varchar(24),
clu varchar(24)
)

System Stored Procedure and openxml

In the first part of the following code, we call a system stored procedure 'sp_xml_prepareddocument'. This calls MSXML parser (Msxmlsql.dll) and this has two constituents, the input @xml and the output @xml_handle. A more thorough explanation and more examples can be found here. The stored procedure provides a representation of the XML that SQL and its tools can use. One of these tools is the openxml which is used to create a Rowset to use within TSQL. The OpenXML takes 3 arguments the handle from the stored procedure @xml_handle, and the Row pattern of the XML '/ROOT/Players'. The last is the flag argument which is used to denote whether the XMML is element or attribute based or a combination of the two. In this case, the flag is 2 which states it is element based XML. This rowset is now inserted into the created table #bundle.

exec sp_xml_preparedocument @xml_handle output, @xml

insert into #bungle(num,ln,fn,pos,clu)
select * from openxml(@xml_handle, '/ROOT/Players', 2) with
(squadnum int ,
LName varchar(24),
Fname varchar(24),
position varchar(8),
team varchar(24))

select * from #bundle
EXEC sp_xml_removedocument @xml_handle

Points of Interest

This is hopefully the end of the XML SQL Odyssey. As a new developer, I find my solution in combining the little bits from many places so I try to show this in what I contribute on here. In this case, the solution can be easily found at MSDN, the only difference here is I pulled in an XML file as opposed to having the XML written out in the SQL.

History

• 20^th November, 2015: Initial version