0
declare @myDoc xml
set @myDoc = '<Form xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.mydomain.org/MySchema.xsd" SectionId="ABCD" Description="Some stuff">
<ProductDescription ProductID="1" ProductName="Road Bike">
<Features>
  <Warranty>1 year parts and labor</Warranty>
  <Maintenance>3 year parts and labor extended maintenance is available</Maintenance>
</Features>
</ProductDescription>
</Form>'

;WITH XMLNAMESPACES(  'http://www.w3.org/2001/XMLSchema-instance' as xsi, 'http://www.w3.org/2001/XMLSchema' as xsd, DEFAULT 'http://www.mydomain.org/MySchema.xsd' )
SELECT @myDoc.value('/Form[@SectionId][0]', 'varchar')

I need to obtain the attribute value of SectionId as a nvarchar ? how do I do it ?...

T and R Mark

markkidduk
  • 13
  • 7

2 Answers2

1

You could write it even simpler:

;WITH XMLNAMESPACES(DEFAULT 'http://www.mydomain.org/MySchema.xsd')
SELECT @myDoc.value('(/Form/@SectionId)[1]', 'VARCHAR(100)') AS SectionId

Since you're never using/referring to any of the xsi or xsd namespaces, there's no need to declare those.

And since you're only fetching one attribute from one element, there's really no point in using the .nodes() function to create an internal "dummy table", either.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Thanks marc, but I don't add the namespaces in, the producer of the xml does so I have to keep them as the SELECT returns NULL without them. The real xml is actually stored in an untyped xml column not a variable of xml type. I am using .nodes() since I cannot guarantee that there will be only one. But I see your point. – markkidduk Nov 11 '10 at 17:51
0
;WITH XMLNAMESPACES(  'http://www.w3.org/2001/XMLSchema-instance' as xsi, 'http://www.w3.org/2001/XMLSchema' as xsd, DEFAULT 'http://www.mydomain.org/MySchema.xsd' )
SELECT Node.value('@SectionId', 'VARCHAR(100)') AS SectionId
FROM @myDoc.nodes('/Form') TempXML (Node);
markkidduk
  • 13
  • 7