Using OPENXML in SQL Server

Another example of using OPENXML to get data into SQL Server from ADO/ADO.NET. This one will fail when the Xml chunk you pass in is bigger than 8000 characters (ntext limitation). However you can pass an XmlDocument stream object to ADO and do that instead and avoid this limitation. MSXML3 or newer needed.

Here’s the Xml:

<cuesheet>
<summary>
<strandtitle><!–[CDATA[Strand title 0]]–></strandtitle>
<programmetitle><!–[CDATA[Programme title 0]]–></programmetitle>
<programmeid><!–[CDATA[0]]–></programmeid>
</summary>
<cuesheetitems>
<cuesheetitem>
<itemtitle><!–[CDATA[IT0]]–></itemtitle>
<composer><!–[CDATA[Composer0]]–></composer>
<musicpublisher><!–[CDATA[Publisher0]]–></musicpublisher>
<log>
<musiccode><!–[CDATA[AA]]–></musiccode>
<duration><!–[CDATA[11:00]]–></duration>
<timein><!–[CDATA[00:00:04]]–></timein>
<timeout><!–[CDATA[00:12:00]]–></timeout>
<visualcue><!–[CDATA[foo]]–></visualcue>
</log>
</cuesheetitem>
</cuesheetitems>
</cuesheet>

Here’s the Stored Procedure:

ALTER PROCEDURE sp_Insert_CueSheet_Openxml
@strXML NTEXT
AS
DECLARE @iDoc INT
EXECUTE SP_XML_PREPAREDOCUMENT @iDoc OUTPUT, @strXML
INSERT INTO CueSheet (StrandTitle, ProgrammeTitle, ProgrammeId)
(SELECT * FROM OPENXML(@iDoc, '/cueSheet/summary', 2)
WITH
(strandTitle VARCHAR(50),
programmeTitle VARCHAR(50),
programmeId VARCHAR(20))
)
DECLARE @cueSheetId INT
SET @cueSheetId = SCOPE_IDENTITY()
DECLARE cueSheetItem_cursor CURSOR FOR
(SELECT * FROM OPENXML(@iDoc, '/cueSheet/cueSheetItems/cueSheetItem', 2)
WITH
(itemTitle VARCHAR(50),
composer VARCHAR(50),
musicPublisher VARCHAR(50))
)
DECLARE @cueSheetItemId INT
DECLARE @xpath VARCHAR(100)
DECLARE @ItemTitle VARCHAR(50)
DECLARE @Composer VARCHAR(50)
DECLARE @Publisher VARCHAR(50)
OPEN cueSheetItem_cursor
FETCH NEXT FROM cueSheetItem_cursor
INTO @ItemTitle, @Composer, @Publisher
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO CueSheetItem (CueSheetID, ItemTitle, Composer, Publisher)
VALUES (@cueSheetId, @ItemTitle, @Composer, @Publisher)
SET @cueSheetItemId = SCOPE_IDENTITY()
SELECT @xpath = '/cueSheet/cueSheetItems/cueSheetItem/itemTitle[text()=''' + @ItemTitle + ''']/../log'
INSERT INTO CueSheetItemLog (CueSheetItemId, MusicCode, Duration, TimeIn, TimeOut, VisualClue)
(SELECT @cueSheetItemId, * FROM OPENXML(@iDoc, @xpath, 2)
WITH
(musicCode CHAR(10),
duration CHAR(10),
timeIn CHAR(10),
timeOut CHAR(10),
visualCue CHAR(10))
)
FETCH NEXT FROM cueSheetItem_cursor
INTO @ItemTitle, @Composer, @Publisher
END
CLOSE cueSheetItem_cursor
DEALLOCATE cueSheetItem_cursor
EXECUTE SP_XML_REMOVEDOCUMENT @iDoc

Here’s the C#

XmlTextReader reader = new XmlTextReader(@"c:tempdummy2.xml");
SqlConnection cnn = new SqlConnection("server=localhost;database=test;trusted_connection=yes");
reader.ReadStartElement("catalog");
cnn.Open();
while (reader.LocalName == "cueSheet")
{
string cueSheetXml = reader.ReadOuterXml();
// Pass the Xml to the database
SqlCommand cmd = new SqlCommand("sp_Insert_CueSheet_Openxml", cnn);
cmd.Parameters.Add("@strXML", SqlDbType.NText);
cmd.Parameters["@strXML"].Value = cueSheetXml;
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
cmd.Dispose();
}
cnn.Close();
cnn.Dispose();
try
{
reader.ReadEndElement();
reader.Close();
}
catch (XmlException)
{
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s