Pls tell me Where I am Wrong! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pls tell me Where I am Wrong!

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 11:16:55

Please Follow my example and tell me Where Is my mistake and i have no results.......
Thank you very much for your time!!!!!

--Drop table #mytable
Create table #MyTable
(
Field1 int identity,
Field2 NvarChar (30),
Field3 NvarChar (50),
Field4 datetime
)

Insert into #Mytable
Select 'Alexander' , 'Developer' , getdate()
Go 100

Select * from #mytable

--Drop table #temp1
Create table #temp1
(
aa datetime ,
bb xml
)

Insert into #temp1
Select getdate() , (Select * from #Mytable for xml auto , ROOT ('ROOT'))

Select * from #temp1

declare @Xmltext xml , @integer int
set @Xmltext = (select bb from #temp1)
EXEC sp_xml_preparedocument @integer OUTPUT, @Xmltext
--Select @Xmltext
SELECT *
FROM OPENXML (@integer, '/ROOT' ,1)
WITH (Field1 nvarchar(10),
Field2 nvarchar(20) )

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 11:32:07

quote:
Originally posted by cmspot

Please Follow my example and tell me Where Is my mistake and i have no results.......
Thank you very much for your time!!!!!

--Drop table #mytable
Create table #MyTable
(
Field1 int identity,
Field2 NvarChar (30),
Field3 NvarChar (50),
Field4 datetime
)

Insert into #Mytable
Select 'Alexander' , 'Developer' , getdate()
Go 100

Select * from #mytable

--Drop table #temp1
Create table #temp1
(
aa datetime ,
bb xml
)

Insert into #temp1
Select getdate() , (Select * from #Mytable for xml auto , ROOT ('ROOT'))

Select * from #temp1

declare @Xmltext xml , @integer int
select @Xmltext = bb from #temp1
EXEC sp_xml_preparedocument @integer OUTPUT, @Xmltext
--Select @Xmltext
SELECT *
FROM OPENXML (@integer, '/ROOT' ,1)
WITH (Field1 nvarchar(10),
Field2 nvarchar(20) )

I sell my mother in law.Is anybody interested?

Could spot only a single change. Try this and see if it works

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 12:46:45

It is not this! I tried it.... It still brings Nulls As results
I really Need help on this

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 13:01:15

What is your xml bb like?

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 13:13:21

quote:
Originally posted by visakh16

What is your xml bb like?

It is the results from this that i execute before:
Insert into #temp1
Select getdate() , (Select * from #Mytable for xml auto , ROOT ('ROOT'))

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 13:32:04

Did you tried printing it out to see the structure?
select bb from #temp1

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 13:42:54

quote:
Originally posted by visakh16

Did you tried printing it out to see the structure?
select bb from #temp1

No! Why Should I do that? I have no idea on Xml...So don't ask if i did because surely i didn't

Help me with an example if you can....

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-09 : 14:00:18

i was asking to check whether value of bb is in right format. Can you post it?

select bb from #temp1

cmspot
Starting Member

44 Posts

Posted - 2008-02-09 : 14:26:51

<ROOT>
<_x0023_Mytable Field1="1" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="2" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="3" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="4" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="5" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="6" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.827" />
<_x0023_Mytable Field1="7" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="8" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="9" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="10" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="11" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="12" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="13" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="14" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="15" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="16" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="17" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="18" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="19" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="20" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="21" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="22" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="23" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="24" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="25" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="26" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="27" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="28" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="29" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="30" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
<_x0023_Mytable Field1="31" Field2="Alexander" Field3="Developer" Field4="2008-02-09T21:24:37.843" />
</ROOT>

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 00:41:23

Change like this & try:-

--Drop table #mytable
Create table #MyTable
(
Field1 int identity,
Field2 NvarChar (30),
Field3 NvarChar (50),
Field4 datetime
)

Insert into #Mytable
Select 'Alexander' , 'Developer' , getdate()
Go 100

Select * from #mytable

--Drop table #temp1
Create table #temp1
(
aa datetime ,
bb xml
)

Insert into #temp1
Select getdate() , (Select * from #Mytable for xml auto , ROOT ('ROOT'))

Select * from #temp1

declare @Xmltext xml , @integer int
select @Xmltext = bb from #temp1
EXEC sp_xml_preparedocument @integer OUTPUT, @Xmltext
--Select @Xmltext
SELECT *
FROM OPENXML (@integer, '/ROOT/_x0023_Mytable' ,1)
WITH (Field1 nvarchar(10),
Field2 nvarchar(20) )

cmspot
Starting Member

44 Posts

Posted - 2008-02-10 : 03:21:13

My Friend Thank you very much.....That was my mistake , I saw it when you asked me to expose my xml results....
Thank you very much for your valueable help and time.....

I sell my mother in law.Is anybody interested?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-10 : 04:02:29

You are welcome. I had thought it would be a small thing that you are missing out. Thats why i asked you to post the xml. Feel free to post a question whenever you are in doubt.

cmspot
Starting Member

44 Posts

Posted - 2008-02-10 : 06:13:10

quote:
Originally posted by visakh16

You are welcome. I had thought it would be a small thing that you are missing out. Thats why i asked you to post the xml. Feel free to post a question whenever you are in doubt.

Thank you friend So Much!!!

I sell my mother in law.Is anybody interested?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources