"Beginner's" XML Resource for SQL Server Developer

Ok, I am looking at BOL for an explanation of some code I came across.
I've used FOR XML in the past to concatenate data, but the code sample I found used type and .value() and some strange formatting, so I used Google to try to find more info. I came across an article on MSDN regarding the value() method that had the following code sample:
SELECT CatalogDescription.value('
     declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
           (/PD:ProductDescription/@ProductModelID)[1] ', 'int') as Result
FROM  Production.ProductModel
WHERE CatalogDescription.exist('
     declare namespace PD="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelDescription";
     declare namespace wm="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelWarrAndMain";

     /PD:ProductDescription/PD:Features/wm:Warranty ') = 1

Open in new window

Then I started coming across stuff like SET @XMLDOC.modify( 'insert  sql:variable("@XML") into (/ROOT[1])'  )

I thought to myself "HUH??! I need some help." My claim to fame using XML is basically using the FOR XML, at times with ROOT() and usually with PATH(").

So, anyone have some suggestions for a primer book dealing specifically with using XML with SQL Server?
LVL 16
Doug BishopDatabase DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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 );

Open in new window

Doug BishopDatabase DeveloperAuthor Commented:
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 (''))

Open 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.
ste5anSenior DeveloperCommented:
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;

Open 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.
Doug BishopDatabase DeveloperAuthor Commented:
**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.
ste5anSenior DeveloperCommented:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.