XQuery: XML data in SQL Server 1

[robz2009]

I'm trying to use Xquery to extract specific info from XML tags but I can't get it to work, all I get is a NULL result. I don't know if the problem is with how the source XML is formed or my xquery statement.

My statement is,

DECLARE @xml xml
SELECT @xml = PasswordPolicy FROM role

SELECT
ppd.value('PasswordLengthMin[1]','int')
FROM @xml.nodes('//PasswordPolicy') pp(ppd)

The xml being populated into @xml is,

<PasswordPolicy xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="

http://www.w3.org/2001/XMLSchema">

<PasswordLengthMin xmlns="urn:travelex.global.enterprise.service">7</PasswordLengthMin>
<PasswordLengthMax xmlns="urn:travelex.global.enterprise.service">50</PasswordLengthMax>
</PasswordPolicy>

If someone can help me out, thanks.

 

[Jamfool]

Have a go with:

DECLARE @xml XML
SELECT @xml = '<PasswordPolicy xmlns:xsi="

http://www.w3.org/2001/XMLSchema-instance"

xmlns:xsd="

http://www.w3.org/2001/XMLSchema">

<PasswordLengthMin xmlns="urn:travelex.global.enterprise.service">7</PasswordLengthMin>
<PasswordLengthMax xmlns="@xml">50</PasswordLengthMax>
</PasswordPolicy>' ;

WITH XMLNAMESPACES ( 'urn:travelex.global.enterprise.service' AS a )
SELECT x.y.value('aasswordLengthMin[1]', 'VARCHAR(50)')
FROM @xml.nodes('/PasswordPolicy') AS x ( y ) ;

SELECT x.y.value('.', 'VARCHAR(50)')
FROM @xml.nodes('
declare namespace a="urn:travelex.global.enterprise.service";
//aasswordLengthMin[1]') AS x ( y ) ;

 

[robz2009]

That works thanks, I'd like to understand the solution also.

Why do you have two select statements? What is the difference between one and another?

 

[robz2009]

You have also changed the text being populated in my @xml variable?

The tag for PasswordLengthMax has changed from.

<PasswordLengthMax xmlns="urn:travelex.global.enterprise.service">50</PasswordLengthMax>

to,

<PasswordLengthMax xmlns="@xml">50</PasswordLengthMax>

Please explain so I can understand this, thanks.

 

[Jamfool]

ooops this

<PasswordLengthMax xmlns="@xml">50</PasswordLengthMax>

should not have changed, it should be as you had it. typo on my part. it should be:

<PasswordLengthMax xmlns="urn:travelex.global.enterprise.service">50</PasswordLengthMax>

The two solutions are just different ways to specify the namespace that you are using. Either by using the WITH syntax, or by declaring a default.

A decent couple of articles that will help you with extra examples:

http://msdn.microsoft.com/en-us/library/ms187013(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms177400(SQL.90).aspx

There are lots of different ways to get to the same result in xml.