SQL Server 2008 XML

speedlearner · March 22nd, 2009, 11:20 AM

I have bought several hundred dollars worth of books and none of them can give me a simple straight answer. I'm a beginner at the game of XML. I would like to store a very simple XML file into a SQL Server 2008 table. I tried to store the XML data using Edit the first 200 command. The server told me that the colums of the table were read only. I tried the Create Table method. No success. I've searched the web for a simple straight answer. I'm so frustrated right now I could spit nails. Is there a simple way to store XML data into a SQL Server 2008 table?

And while we are on the subject, is there a simple way to have the eXist server find an XML file stored in INetPub? eXist seems to store its XML files in a mystery spot. I would love to know where that spot is so that I can store my own XML files there.

philip_cole · March 22nd, 2009, 12:32 PM

Hi
There is a lot of information about using XML in SQL Server 2008 on MSDN. The section for using it in tables is at http://msdn.microsoft.com/en-us/library/bb510446.aspx but admittedly it is not all clear.

Here is a simple example for creating a table with an untyped (xml is not validated) xml column, then inserting and selecting the data:

sql Code:

-- create the table - note the XML datatype
CREATE TABLE XmlSample (
    ID INT PRIMARY KEY,
    SampleData XML NOT NULL
);

-- add a row - xml is added the same way as a normal VARCHAR etc.
INSERT INTO XmlSample (ID, SampleData) VALUES (1, '
    <Root>
        <Foo name="bob">
            <Bar />
        </Foo>
        <Foo name="dave">
            <Bar />
        </Foo>
    </Root>
');

-- get data back - SSMS lets you click the XML cell and show the full document
SELECT * FROM XmlSample;

(I am using SQL Server 2005, but I can't see any changes required for 2008. Please correct me if I'm wrong)

HTH
Phil