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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Programming » XML » The argument 1 of XML datatype method nodes...

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

Rate Topic

Display Mode

Topic Options

Author

Message

suvo.kundu

Posted Wednesday, September 12, 2012 5:02 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80

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

Post #1357892

arthurolcot

Posted Wednesday, September 12, 2012 5:27 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:04 AM
Points: 1,722, Visits: 1,404

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.

Post #1357905

Mark-101232

Posted Wednesday, September 12, 2012 5:31 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:34 AM
Points: 1,500, Visits: 18,179

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)

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1357907

arthurolcot

Posted Wednesday, September 12, 2012 5:36 AM

SSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:04 AM
Points: 1,722, Visits: 1,404

Nice.... I like that solution :)

Post #1357913

suvo.kundu

Posted Wednesday, September 12, 2012 7:33 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80

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

Post #1357967

Mark-101232

Posted Wednesday, September 12, 2012 7:38 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 4:34 AM
Points: 1,500, Visits: 18,179

Try adding

ORDER BY SN,NodeName,NodeValue

to the end of the query

____________________________________________________

How to get the best help on a forum

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

Never approach a goat from the front, a horse from the rear, or a fool from any direction.

Post #1357969

suvo.kundu

Posted Sunday, September 16, 2012 2:08 AM

Grasshopper

Group: General Forum Members
Last Login: Saturday, January 12, 2013 8:31 AM
Points: 17, Visits: 80

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)

actually the xml that i am fetching is very large approx 5mb in size with lots of child nodes.
it is taking a lot of time..plz suggest alternative option with this query.

Post #1359868

« Prev Topic | Next Topic »

Permissions