How to extract XML content in my SP? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to extract XML content in my SP?

Author

Topic

petre
Starting Member

11 Posts

Posted - 2010-09-06 : 04:50:09

hi friends,

my Data access layer passes DB SQLparameter of type XML tp my SP, sp_save_user, the xml sqlparameter contain all roles that is assicated with a User, so the XML content will like this:

<Roles>
<Role>
<userID>10</UserID>
<RoleID>200</RoleID>
<Role>
<Role>
<userID>10</UserID>
<RoleID>300</RoleID>
<Role>
<Role>
<userID>10</UserID>
<RoleID>400</RoleID>
<Role>
</Roles>

Now i need to create an XML input parameter in the SP so that i can store the passing XML value.

1. How do i create such parameter in my SP?
2. How do i extract the XML content in that XML parameter in my SP?

thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-06 : 05:32:21

create procedure getmyxmlnow
(
data xml
)
as

set nocount on

insert targettable
select ...
from ...

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-09-06 : 05:40:35

And here is how you parse your xml

DECLARE	@Data XML

SET	@Data = '
<Roles>
<Role>
<UserID>10</UserID>
<RoleID>200</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>300</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>400</RoleID>
</Role>
</Roles>'

SELECT	n.value('UserID[1]', 'INT') AS UserID,
	n.value('RoleID[1]', 'INT') AS RoleID
FROM	@Data.nodes('Roles/Role') AS f(n)

N 56°04'39.26"
E 12°55'05.63"

Scal
Starting Member

24 Posts

Posted - 2011-04-16 : 18:11:29

Hi;
any idea why the working example just above does not work when the XML contains a xmlns definition like:

SET @Data = '
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<Roles>
<Role>
<UserID>10</UserID>
<RoleID>200</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>300</RoleID>
</Role>
<Role>
<UserID>10</UserID>
<RoleID>400</RoleID>
</Role>
</Roles>
</html>'

If you do:
;WITH XMLNAMESPACES ('http://www.w3.org/1999/xhtml' AS ns)
SELECT n.value('UserID[1]', 'INT') AS UserID,
n.value('RoleID[1]', 'INT') AS RoleID
FROM @Data.nodes('/ns:html/ns:Roles/ns:Role') AS f(n)

each cell gets a value of NULL

Thanks

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-16 : 21:02:56

Need namespaces for the elements whose value you are trying to get - they are in the same namespace as ns.


;WITH XMLNAMESPACES ('http://www.w3.org/1999/xhtml' AS ns)
SELECT	n.value('ns:UserID[1]', 'INT') AS UserID,
n.value('ns:RoleID[1]', 'INT') AS RoleID
FROM	@Data.nodes('/ns:html/ns:Roles/ns:Role') AS f(n)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-16 : 22:10:37

To add to my previous post, you could declare the one namespace you have as default:

;WITH XMLNAMESPACES (DEFAULT 'http://www.w3.org/1999/xhtml' )
SELECT	n.value('UserID[1]', 'INT') AS UserID,
n.value('RoleID[1]', 'INT') AS RoleID
FROM	@Data.nodes('/html/Roles/Role') AS f(n)

Scal
Starting Member

24 Posts

Posted - 2011-04-16 : 22:41:04

Your additional post is what I did, thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources