SQL Server XML

NAMASPACE

Global Definition:

;WITH XMLNAMESPACES  (
'xxx1' AS heads,
'xxx2' AS nodes
)
Locality Definition:
SELECT tb.xml_message.Query(N'
declare namespace nodes = "xxx";
/nodes:Node/nodes:Type
') as res
 FORM tb

 <Node xmlns="xxx">
     <Type>type1<Type>
     <Name>name1<Name>
     <Number>001<Number>
 </Node>

Query XML/Value

 ;WITH XMLNAMESPACES  (
'xxx1' AS heads,
'xxx2' AS nodes
)
 --Return xml: <Type>type1</Type>
 SELECT tb.xml_message.Query(N'/nodes:Node/nodes:Type
') as res
 FORM tb
 --Return value: type1
 SELECT tb.xml_message.Query(N'/nodes:Node/nodes:Type/text()
') as res
 FORM tb
 SELECT tb.xml_message.value(N'(/nodes:Node/nodes:Type/text())[1]
',N'varchar(20)') as res
 FORM tb

Where

 ;WITH XMLNAMESPACES  (
'xxx1' AS heads,
'xxx2' AS nodes
)
 SELECT * FROM tb WHERE xml_message.value(N'(/nodes:Node/nodes:Type/text())[1]
',N'varchar(20)') = 'type1'

Query multiple value from xml

 ;WITH XMLNAMESPACES  (
'xxx1' AS heads,
'xxx2' AS nodes
)
--Return: type1 name2
--        type2 name2
SELECT xml_nodes.query(N'/nodes:Type/text()') as  type,xml_nodes.query(N'/nodes:Name/text()') as  name
FROM
(SELECT T.C.query(N'.') as xml_nodes FROM tb
CROSS APPLY xml_message.nodes(N'/nodes:Node') AS T(C))

Tips:

--This will return null without child select
SELECT T.C.query(N'/nodes:Name/text()') as xml_nodes FROM tb
CROSS APPLY xml_message.nodes(N'/nodes:Node') AS T(C)