The argument 1 of XML datatype method nodes must be string literal.plz help
-
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
-
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.
-
Use sql:variable
set @starttag ='XML'set @root ='Provider'SELECTdense_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
-
Nice.... I like that solution
-
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
-
Try adding
ORDER BY SN,NodeName,NodeValueto the end of the query
____________________________________________________
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 -
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.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply