Category Archives: XML

Shred XML Attributes in SQL 2008


Scenario:   Need to shred attribute values out of an XML Variable in SQL 2008

Solution:  Use XQuery and Nodes/Value to pull out the needed values.

Snippet:

declare @XMLMessage xml
set @XMLMessage = '<XMLMessageName xmlns:xsd = "http://www.w3.org/2001/XMLSchema" xmlns:xsi = "http://www.w3.org/2001/XMLSchema-instance" 
 IsResend = "false" StoreID= "195" PartnerID = "-1">
    <LogTime StartDateTime = "2011-06-24T08:47:40-07:00">
        <SalesEvents>
            <SaleEvent
                PartnerID = "-1"
                Comment = ""
                UPC = ""
                Keyword = ""
                Coop = ""
                Category= ""
                Region= "-1"
                InsertionType = "Normal"
                ClerkNumber= "0010068-001"
                Client = ""
                ISCI = ""
                Manufacturer = "Justin"
                Item= "Stuff"
                SaleType= "Credit"
                SaleNumber = "325077"
                BreakID = "-1"
                Sequence = "1"/>
        </SalesEvents>
    </LogTime>
</XMLMessageName >'

select
	ClerkNumber= t.o.value('@ClerkNumber', 'varchar(50)')
	, StoreID= t.o.value('../../../@StoreID', 'integer')

from @XMLMessage.nodes('XMLMessageName /LogTime/SalesEvents/SaleEvent') as t(o)
where t.o.value('@Sequence', 'integer') = 1

Results:

ClerkNumber                                        StoreID            
-------------------------------------------------- -------------------
0010068-001                                        195                
(1 row(s) affected)