"Beginner's" XML Resource for SQL Server Developer

It's called XQuery. It's a generic XML query language.

SQL Server supports only a subset of it: XQuery Language Reference (SQL Server).

And often you only use XPath to select elements and values with the nodes() and values() methods.

For example sorting a XML fragment:

DECLARE @Data XML = N'
<level1 name="3">      
    <level2 name="f" />
    <level2 name="e" />
    <level2 name="d" />
</level1>
<level1 name="2">
    <level2 name="c" />    
    <level2 name="b" />
</level1>
<level1 name="1">      
    <level2 name="a" />
</level1>
';

SELECT  Levels.query('
        for $level1 in /level1
        order by $level1/@name ascending
        return
            <level1 name="{$level1/@name}">{
                for $level2 in $level1/level2
                order by $level2/@name ascending
                return $level2
            }</level1>
        ')
FROM    @Data.nodes('.') Levels ( Levels );

Select allOpen in new window

ste5an: The links provide more info and tell me what it is I was looking at, but neither your example or the links provide any kind of a primer for someone who has either no or very limited exposure to XML. From what I've seen in the links, it tells me that, if I want to, I can do a LOT more than just concatenate rows together into a string, which is basically ALL I've used FOR XML for (which I don't even see mentioned in any of the links.

For the most part, I've written things like:

(SELECT m.name
FROM   table
WHERE  a.indexid = m.indexid 
ORDER BY columnname
FOR XML PATH (''))

Select allOpen in new window

as part of a correlated subquery to concatenate rows into a string.

The code I've come across looks intriguing and I thought maybe I'd like to get to know a bit more about it. I need a step-by-step guide that would explain Levels.query, $level1/@name, FROM @Data.nodes('.') Levels ( Levels ) (taking pieces of your code example), what they mean and how they are used. Not sure I want to get DEEP into this, but would be nice to know something about it.

You need to make yourself familiar with XQuery and XPath. These are XML techniques. And they form an entire language of their own.

But for your question.. The query itself is pretty simple. It looks only blown up due to the necessary use of XML namespaces (another complex area in XML).

It is easier to understand, when it is rewritten as:

USE AdventureWorks2014;
GO

WITH XMLNAMESPACES ( 
	'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription' AS PD,
	'http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain' AS wm 
	) 
    SELECT  CatalogDescription.value('/PD:ProductDescription[1]/@ProductModelID', 'int') AS Result
    FROM    Production.ProductModel
    WHERE   CatalogDescription.exist('/PD:ProductDescription/PD:Features/wm:Warranty') = 1;

Select allOpen in new window

value('/PD:ProductDescription[1]/@ProductModelID', 'int') means that the product model ID attribute from the first prudoc description element.
And exist('/PD:ProductDescription/PD:Features/wm:Warranty') = 1 is test, which only returns rows where the warranty element exists and has a value of 1.

**So, anyone have some suggestions for a primer book dealing specifically with using XML with SQL Server?**

Please, I don't mean to be rude. I could probably sit at my desk all day long and click links and read stuff on MSDN, Wikipedia and w3.org for several days.

However, I would prefer, as stated above, to find a resource (i.e. book) that will start from the ground up explaining all this to someone who pretty much knows nothing (ok, not nothing, but pretty close to it), gives step-by-step examples (I learn by examples) and will put it all together.

Preferably something with SQL Server in mind, since that is pretty much the platform I will be working on.