Blog ala Vidar

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:

USE tempdb;

IF EXISTS (SELECT *
		FROM sys.sysobjects
		WHERE name = 'xmltest1')
	DROP TABLE xmltest1

CREATE TABLE xmltest1 (
	ID INT,
	Name NVARCHAR(200)
)

INSERT INTO xmltest1
  (ID, Name)
VALUES
  (1, 'Jens Stoltenberg'),
  (2, 'Erna Solberg'),
  (3, 'Siv Jensen'),
  (4, 'Lars Sponheim')

SELECT *
	FROM xmltest1
	FOR XML AUTO

SELECT *
	FROM xmltest1
	FOR XML AUTO, ELEMENTS

DECLARE @MyXML AS XML
SET @MyXML = (SELECT *
		FROM xmltest1
		FOR XML PATH('Politician'), ROOT('Politicians'))

SELECT @MyXML
/*
<Politicians>
  <Politician>
    <ID>1</ID>
    <Name>Jens Stoltenberg</Name>
  </Politician>
  <Politician>
    <ID>2</ID>
    <Name>Erna Solberg</Name>
  </Politician>
  <Politician>
    <ID>3</ID>
    <Name>Siv Jensen</Name>
  </Politician>
  <Politician>
    <ID>4</ID>
    <Name>Lars Sponheim</Name>
  </Politician>
</Politicians>
*/

DECLARE @docHandle AS INTEGER
EXEC sp_xml_preparedocument @docHandle OUTPUT, @MyXML

SELECT *
	FROM OPENXML(@docHandle, N'/Politicians/Politician')
	WITH (ID INTEGER 'ID', Name NVARCHAR(200) 'Name')

SELECT @MyXML.query('/Politicians/Politician/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.