I'm trying to get a grip on the XML to DB concept, so I executed the following, copy/pasted from the article.
I don't understand why I'm getting 0 rows returned.
I most likely forgot something or I need clarification on the subject.
Pointers would be appreciated.
Thanks,
Henk
PS: For a reason that's beyond me, the code snippet after "-- final code line" is automagically inserted by the web interface of this post-editor.
The line ending in " -- final code line" delimits the code that I executed
create XML SCHEMA COLLECTION stat AS
'
'
CREATE TABLE [dbo].[XML_TestTbl](
[XML_ID] [bigint] IDENTITY(1,1) NOT NULL primary key,
[Table_NM] AS (('XML_TestTbl')),
[XML_Data] [xml] NOT NULL,
[Schema_NM] [varchar](20) NOT NULL,
[InsertDate] [datetime] NOT NULL default ( getdate() ) )
go
declare @verify table (XML_Data XML(stat) );
declare @xmlvar1 xml (stat);
set @xmlvar1 = '
6
prd1234
12345
'
Insert into @verify(XML_Data)
Select XML_Data from XML_TestTbl where schema_nm = 'stat';
select
[XML_Data].query('//customerid').value('.','integer') as customerid,
[XML_Data].query('//prodno').value('.','varchar(max)') as prodno,
[XML_Data].query('//userid').value('.','integer') as userid
from dbo.XML_TestTbl
where schema_nm = 'stat' -- final code line