Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

The argument 1 of XML datatype method nodes must be string literal.plz help Expand / Collapse
Author
Message
Posted Wednesday, September 12, 2012 5:02 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, September 12, 2012 5:27 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:37 AM
Points: 2,550, Visits: 1,613

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
Posted Wednesday, September 12, 2012 5:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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
Posted Wednesday, September 12, 2012 5:36 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 11, 2014 1:37 AM
Points: 2,550, Visits: 1,613
Nice.... I like that solution :)
Post #1357913
Posted Wednesday, September 12, 2012 7:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Wednesday, September 12, 2012 7:38 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 8, 2014 6:13 AM
Points: 1,694, Visits: 19,550
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
Posted Sunday, September 16, 2012 2:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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 »

Add to briefcase

Permissions Expand / Collapse