SQL Server 2008 - basic question on shredding XML

Question

Hello - I'm somewhat new to using XML in sql server, and am having a problem getting the syntax right - and for that matter, probably have some basic confusion about referencing XML elements.

Anyway, I'll need to shred XML like the below out into columns. Seems simple, but I can't seem to get the nodes right - or for that matter I don't think I'm doing the xmlnamespaces correctly either. I haven't found much that's helpful online yet. Would greatly appreciate any hints!

thanks, Sylvia

DECLARE @pStepLogXML  xml;
select     @pStepLogXML = '<?xml version="1.0" encoding="UTF-8"?>
<!--Sample XML file generated by XMLSpy v2010 (http://www.altova.com)--&gt;
<Applescommon:ActivityStepLogList xsi:schemaLocation="urn:SampleCompany:Apples:common:v1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:Applescommon="urn:SampleCompany:Apples:common:v1">
    <Applescommon:ActivityStepLog>
        <Applescommon:GUID>3F2504E0-4F89-11D3-9A0C-0305E82C3301</Applescommon:GUID>
        <Applescommon:ContextID>3F2504E0-4F89-11D3-9A0C-0305E82C3302</Applescommon:ContextID>
        <Applescommon:LogType>PreStep</Applescommon:LogType>
        <Applescommon:SourceID>com.SampleCompany.e3.es.Apples.common.ApplesCommonMessageHandler.processMessage</Applescommon:SourceID>
        <Applescommon:LogContent><![CDATA[
<TestXML><XXXXXX></TestXML>
        ]]></Applescommon:LogContent>
        <Applescommon:LogTime>2001-12-17T09:30:47Z</Applescommon:LogTime>
    </Applescommon:ActivityStepLog>
    <Applescommon:ActivityStepLog>
        <Applescommon:GUID>3F2504E0-4F89-11D3-9A0C-0305E82C3303</Applescommon:GUID>
        <Applescommon:ContextID>3F2504E0-4F89-11D3-9A0C-0305E82C3302</Applescommon:ContextID>
        <Applescommon:LogType>PostStep</Applescommon:LogType>
        <Applescommon:SourceID>com.SampleCompany.e3.es.Apples.common.ApplesCommonMessageHandler.processMessage</Applescommon:SourceID>
        <Applescommon:LogContent><![CDATA[
<TestXML><XXXXXX></TestXML>
        ]]></Applescommon:LogContent>
        <Applescommon:LogTime>2001-12-17T09:30:47Z</Applescommon:LogTime>
    </Applescommon:ActivityStepLog>
</Applescommon:ActivityStepLogList>';

with xmlnamespaces (default 'urn:Applescommon:ActivityStepLogList:v1')
select 
   --convert(varchar(64), MyTables.MyColumns.value('Applescommon:GUID/text()', n'nvarchar(100)')
   -- MyTable.MyColumns.value('Applescommon:GUID/text()', N'nvarchar(50)') AS Test,
   --,
   1
from @pStepLogXML.nodes('/Applescommon:ActivityStepLog' )
    as T(c)

EDIT:

Okay, figured it out. I basically had the xmlnamespaces and nodes completely messed up. I thought I copied them (changing them appropriately) from code that worked, so if anyone would care to point me to a good coherent explanation of these, I'll give them answer credit.

Also, I wasn't having problems with getting the column data, so I just put 1 for a placeholder - below I actually put one of the real columns.

Thanks for reading!

Sylvia

with xmlnamespaces (default 'urn:SampleCompany:Apples:common:v1')
select 
   --convert(varchar(64), MyTables.MyColumns.value('Applescommon:GUID/text()', n'nvarchar(100)')
   -- MyTable.MyColumns.value('Applescommon:GUID/text()', N'nvarchar(50)') AS Test,
   --,
   T.c.query('SourceID/text()')
   ,1
from @pStepLogXML.nodes('/ActivityStepLogList/ActivityStepLog' )
    as T(c)

Answer 1

XQuery isn't easy, until you get the hang of it - I don't really have a "magic" site that just tells you everything and then you know.... the best intro article I read was

SQL Server 2005 XQuery and XML-DML (a 3-part article series)

and also: An Introduction to XQuery in SQL Server 2005

Most of all: practice, practice, practice :-) Nothing new, right??

Now your last query is OK - but you could make it even easier:

with xmlnamespaces (default 'urn:SampleCompany:Apples:common:v1')
select 
   T.C.value('(GUID)[1]', 'nvarchar(50)') AS Test,
   T.C.value('(ContextID)[1]', 'nvarchar(50)') AS ContextID,
   T.C.value('(SourceID)[1]', 'nvarchar(50)') AS SourceID,
   --,
   1
from 
    @pStepLogXML.nodes('/ActivityStepLogList/ActivityStepLog') as T(c)

Using the .value() function (instead of .query()) gives you the "atomic" values right away, and you can also define what output data type that value should be.