Shred XML Attributes

Submitted by:Jhon Brain

Date added:08 May, 2013

Category:SQL

Shred attribute values out of an XML Variable in SQL 2008

Tags: xml variable

Code 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
 
 

Comments