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)
 extrace from xml Column

Author  Topic 

chetanb3
Yak Posting Veteran

52 Posts
Posted - 2008-06-02 : 06:47:53
I have following XML in my table column
I want to extract "ExecuteSoftwareadvertGuid"
Please help.


<vItem>
<tokens />
<remoteExecAdvanced><priority value="Normal" compatibility="Exclusive" />
<kill enforce="True" maxMinutes="30" />
<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
</remoteExecAdvanced>
<ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530"detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-02 : 06:56:24
The XML is malformed.
You have terminated remoteExecAdvanced twice.

<vItem>
	<tokens/>
	<remoteExecAdvanced><priority value="Normal" compatibility="Exclusive" />
		<kill enforce="True" maxMinutes="30" />
		<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
	</remoteExecAdvanced>
	<ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530"detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 07:05:01
quote:
Originally posted by Peso

The XML is malformed.
You have terminated remoteExecAdvanced twice.

<vItem>
	<tokens/>
	<remoteExecAdvanced><priority value="Normal" compatibility="Exclusive" />
		<kill enforce="True" maxMinutes="30" />
		<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
	</remoteExecAdvanced>
	<ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530"detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>


E 12°55'05.25"
N 56°04'39.16"

Nope. Thats the termination of priority node
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-02 : 07:12:09
Ah, good spot!


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-02 : 07:18:03
Still thinks there are invalid characters.
DECLARE @Yak XML,
	@iDoc INT

SET	@Yak = '
<vItem>
	<tokens/>
	<remoteExecAdvanced>
		<priority value="Normal" compatibility="Exclusive" />
		<kill enforce="True" maxMinutes="30" />
		<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
	</remoteExecAdvanced>
	<ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>
'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @Yak

SELECT	*
FROM	OPENXML (@idoc, '/vItem',1)
WITH	(
		ExecuteSoftwareadvertGuid  varchar(40)
	)

EXEC sp_xml_removedocument @iDoc
The error is the line with ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530".
There is no distinction between element and attribute.


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-02 : 07:21:16
quote:
Originally posted by Peso

Still thinks there are invalid characters.
DECLARE @Yak XML,
	@iDoc INT

SET	@Yak = '
<vItem>
	<tokens/>
	<remoteExecAdvanced>
		<priority value="Normal" compatibility="Exclusive" />
		<kill enforce="True" maxMinutes="30" />
		<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
	</remoteExecAdvanced>
	<ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>
'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @Yak

SELECT	*
FROM	OPENXML (@idoc, '/vItem',1)
WITH	(
		ExecuteSoftwareadvertGuid  varchar(40)
	)

EXEC sp_xml_removedocument @iDoc
The error is the line with ExecuteSoftwareadvertGuid="452450da-c389-435c-a744-ec859f473530".
There is no distinction between element and attribute.


E 12°55'05.25"
N 56°04'39.16"

I think he proabably meant this
<ExecuteSoftwareadvert Guid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts
Posted - 2008-06-02 : 07:29:34
And let the guesses begin...
DECLARE @Yak XML,
	@iDoc INT

SET	@Yak = '
<vItem>
	<tokens/>
	<remoteExecAdvanced>
		<priority value="Normal" compatibility="Exclusive" />
		<kill enforce="True" maxMinutes="30" />
		<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
	</remoteExecAdvanced>
	<ExecuteSoftwareadvert Guid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>
'

EXEC sp_xml_preparedocument @iDoc OUTPUT, @Yak

SELECT	*
FROM	OPENXML(@idoc, '/vItem/ExecuteSoftwareadvert',1)
WITH	(
		GUID		UNIQUEIDENTIFIER	'@Guid',
		detectionGuid	UNIQUEIDENTIFIER	'@detectionGuid'
	)

EXEC sp_xml_removedocument @iDoc


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

chetanb3
Yak Posting Veteran

52 Posts
Posted - 2008-06-03 : 08:05:35
what sp_xml_preparedocument proc do ?
Peso Could you please explain me in details.
i tried with sp_helptext sp_xml_preparedocument but it gives "Server internal" as result
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-03 : 08:14:13
quote:
Originally posted by chetanb3

what sp_xml_preparedocument proc do ?
Peso Could you please explain me in details.
i tried with sp_helptext sp_xml_preparedocument but it gives "Server internal" as result


Have a look at OPENXML in books online.
Go to Top of Page

chetanb3
Yak Posting Veteran

52 Posts
Posted - 2008-06-04 : 01:58:33
DECLARE @Yak XML,
@iDoc INT
SET @Yak = '
<vItem>
<tokens/>
<remoteExecAdvanced>
<priority value="Normal" compatibility="Exclusive" />
<kill enforce="True" maxMinutes="30" />
<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
</remoteExecAdvanced>
<ExecuteSoftwareadvert Guid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>'
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Yak
SELECT *
FROM OPENXML(@iDoc, '/vItem/ExecuteSoftwareadvert',1)
WITH (
Guid UNIQUEIDENTIFIER ,
detectionGuid UNIQUEIDENTIFIER
)



I have corrected query to some extent.
but my problem is how can i use this in my query?
i have number of columns other tham these two.

also i have xml in table column.

please anyone guide me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts
Posted - 2008-06-04 : 02:03:30
quote:
Originally posted by chetanb3

DECLARE @Yak XML,
@iDoc INT
SET @Yak = '
<vItem>
<tokens/>
<remoteExecAdvanced>
<priority value="Normal" compatibility="Exclusive" />
<kill enforce="True" maxMinutes="30" />
<platform configuredProviderGuid="00000000-0000-0000-0000-000000000000"/>
</remoteExecAdvanced>
<ExecuteSoftwareadvert Guid="452450da-c389-435c-a744-ec859f473530" detectionGuid="00000000-0000-0000-0000-000000000000" />
</vItem>'
EXEC sp_xml_preparedocument @iDoc OUTPUT, @Yak
SELECT *
FROM OPENXML(@iDoc, '/vItem/ExecuteSoftwareadvert',1)
WITH (
Guid UNIQUEIDENTIFIER ,
detectionGuid UNIQUEIDENTIFIER
)



I have corrected query to some extent.
but my problem is how can i use this in my query?
i have number of columns other tham these two.

also i have xml in table column.

please anyone guide me.




Can you specify your original requirement with some data sample from table?
Go to Top of Page

chetanb3
Yak Posting Veteran

52 Posts
Posted - 2008-06-04 : 09:35:27
imagine that above xml is in column 'xmlCol' table T1.
there are many column in T1. i want to extract Guid from tag "ExecuteSoftwareadvert" in my select also i
have join on this Guid with other Table say T2.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site