Blog ala Vidar

SQL, AppFrame and other cool technologies

Tag Archives: XML

SQLXML

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.

Advertisements

SQL Query tips

Happy new year, guys!

I’ve been very busy lately. In Ølen we’re making a new framework, based on winforms. The first thing we did was DROP TABLE stbl_MSAccess%. So long, suckers! Now there’s no turning back! (PS!! We will still support access in AppFrameR2). So, what are we doing? Well, we’re making a framework, based on .NET 2.0 and controls from DevExpress. We have already started making some forms and it’s starting to look pretty nice! Today I did a quick test. Made a simple form with a couple of subforms. Took me not more than 15 minutes! I hope everyone’s looking forward to this new framework 🙂

I ran into a little problem when I was making a form today. In one of the subforms we need to list all mails from/to one address, with from, to, cc, subject and the body. The problem was that the users want’s CCs in ONE column, comma separated, and it’s stored as one record pr address in the table. How did I solve this? Of course, with a little help from Torgeir and some XML-queries

SELECT M.FromEMail, M.Subject, MC.TextHTML, M.PrimKey,
  (SELECT (SELECT [data()] = EMail + ','
		FROM stbl_Mail_MessagesRecipients
		WHERE MessageRef=M.PrimKey
		  AND Type='To'
		FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS ToEMail,
  (SELECT (SELECT [data()] = EMail + ','
		FROM stbl_Mail_MessagesRecipients
		WHERE MessageRef=M.PrimKey
		  AND Type='Cc'
		FOR XML PATH(''), TYPE).value('.', 'varchar(max)')) AS CCEMail
	FROM stbl_Mail_Messages AS M
	INNER JOIN stbl_Mail_MessagesContent AS MC ON M.PrimKey = MC.MessageRef