Beispiel "Loop durch XML-Nodes":
declare @XmlParameters xml = '<Product xmlns="http://my.domain.info/ProductData" ID="1" /><Product xmlns="http://my.domain.info/ProductData" ID="2" />'
declare @Count smallint=@XmlParameters.value('declare default element namespace"http://my.domain.info/ProductData"; (count(/Product))[1]', 'smallint')
set @Index = 1
while @Index<=@Count
begin
select fielda from tbl1
where fieldb=@XmlParameters.value('declare default element namespace"http://my.domain.info
/ProductData"; (/Product[sql:variable("@Index")]/@ID)[1]', 'char(1)')
set @Index = @Index + 1
end
Beispiel "Add Attribute":
declare @Statistic xml = '<bbb/>'
declare @XmlOut xml = '<aaa/>'
set @XmlOut.modify('
insert sql:variable("@Statistic")
into (/aaa)[1]')
select @XmlOut
Ergebnis:
<aaa>
<bbb />
</aaa>
Beispiel "Namespace":
WITH XMLNAMESPACES( 'http://pcs-software.it/exchange.xsd' AS "bw")
select top 10 E0MaC, E2RID, E2RTS, E2XEN.value('(/XML/bw:xenusData/bw:PersonalData/bw:Country/text())[1]', 'nvarchar(max)') as x
from [CentralDB].[dbo].[tblCdbE2]