The argument 1 of XML datatype method nodes must be string literal.plz help

Question

The argument 1 of XML datatype method nodes must be string literal.plz help

suvo.kundu

SSC Eights!

Points: 953
More actions
September 12, 2012 at 5:02 am

#100853

declare @starttag varchar(100)
declare @root varchar(100)
set @starttag ='XML'
set @root ='XML'
Declare @xml xml
Set @xml =
'<XML>
<Provider>
<providerID>1</providerID>
<Address>address1</Address>
</Provider>
<Provider>
<providerID>2</providerID>
<Address>address2</Address>
</Provider>
<Provider>
<providerID>3</providerID>
<Address>address3</Address>
<city>kol</city>
</Provider>
</XML>'
SELECT
dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',
NodeName = C.value('local-name(.)', 'varchar(50)')
, NodeValue = C.value('(.)[1]', 'varchar(50)')
FROM @xml.nodes('/XML/Provider/*') AS T(C)
But if the sql is
SELECT
dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',
NodeName = C.value('local-name(.)', 'varchar(50)')
, NodeValue = C.value('(.)[1]', 'varchar(50)')
FROM @xml.nodes('/'+@starttag +'/'+@root+'/*') AS T(C)
it gives error as
The argument 1 of XML datatype method nodes must be string literal..plz help

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 1

The argument 1 of XML datatype method nodes must be string literal..plz help

This means that the argument that you are specifying within the nodes() function must be a string literal like you have done in the first SELECT and not a constructed string as you are trying to use in the second SELECT. You cannot use a variable either.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 2

Use sql:variable

set @starttag ='XML'

set @root ='Provider'

SELECT

dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',

NodeName = C.value('local-name(.)', 'varchar(50)')

, NodeValue = C.value('(.)[1]', 'varchar(50)')

FROM @xml.nodes('/*[local-name(.)=sql:variable("@starttag")]/*[local-name(.)=sql:variable("@root")]/*') AS T(C)

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Arthur Olcot SSCertifiable Points: 6639 More actions · Answer 3

Arthur Olcot

SSCertifiable

Points: 6639

September 12, 2012 at 5:36 am

#1536660

Nice.... I like that solution 🙂

suvo.kundu SSC Eights! Points: 953 More actions · Answer 4

actually the out put of the query changes using ur suggestion.The value of SN column changes

set @starttag ='XML'

set @root ='Provider'

SELECT

dense_rank() OVER (ORDER BY C.value('local-name(.)', 'varchar(50)')) AS 'SN',

NodeName = C.value('local-name(.)', 'varchar(50)')

, NodeValue = C.value('(.)[1]', 'varchar(50)')

FROM @xml.nodes('/*[local-name(.)=sql:variable("@starttag")]/*[local-name(.)=sql:variable

OUTPUt:-

SN NodeName NodeValue

1 Address Address3

3 providerID 2

3 providerID 1

1 Address Address2

1 Address Address1

3 providerID 3

2 City Kol

My desired out put is

SN NodeName NodeValue

1 Address address1

1 Address address2

1 Address address3

2 city kol

3 providerID 1

3 providerID 3

3 providerID 2

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 5

Try adding

ORDER BY SN,NodeName,NodeValue

to the end of the query