Ever tried working with XML in SQL Server? Some find it very difficult. I find it.. Interesting? I’m not sure. My opinion is that if possible, stay away from XML, but if you need to use it, here’s some quick starter tips:
IF EXISTS (SELECT *
WHERE name = 'xmltest1')
DROP TABLE xmltest1
CREATE TABLE xmltest1 (
INSERT INTO xmltest1
(1, 'Jens Stoltenberg'),
(2, 'Erna Solberg'),
(3, 'Siv Jensen'),
(4, 'Lars Sponheim')
FOR XML AUTO
FOR XML AUTO, ELEMENTS
DECLARE @MyXML AS XML
SET @MyXML = (SELECT *
FOR XML PATH('Politician'), ROOT('Politicians'))
DECLARE @docHandle AS INTEGER
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MyXML
FROM OPENXML(@docHandle, N'/Politicians/Politician')
WITH (ID INTEGER 'ID', Name NVARCHAR(200) 'Name')
/* Clean up*/
EXEC sp_xml_removedocument @docHandle
DROP TABLE xmltest1
I’m creating a test-table with two columns, and adding some records. These are well known Norwegian politicians. If you run this script, you’ll see it will return 5 record sets. The first one is from FOR XML AUTO. This means it will have one element pr record, with attributes for each field. The next one (FOR XML AUTO, ELEMENTS) will return one element pr record, with child elements for each field. The third (SELECT @MyXML) will have one root element, Politicians, with one element (Politician) pr record, and child elements for each field, as shown in the comments.
The fourth is using OPENXML instead of FOR XML. This means I have to “prepare” a document for the SQL server. I’m adding it, and getting back an ID to that document (using @docHandle). With this I’m querying the XML, to get SQL results, so the fourth is actually the same as running SELECT * FROM xmltest1. Be aware that I’m naming the fields. ID INTEGER ‘ID’. This means that it’s picking the ID element, and putting it in the ID field. For getting attributes, you’ll use @ID. For example ID INTEGER ‘@ID’. You can also query parent elements by doing ‘../ID’ or ‘../@ID’.
The last one is querying the @MyXML variable. Here I’m getting a XML based on @MyXML. I only get the Name elements.
NB!!! I’m using INSERT with multiple values. This is only supported by SQL Server 2008. The rest is supported by both 2005 and 2008.