SQL- fetch value from XML parent node (separately)

Question

In my below Query it's return all the recode set as a XML into a single variable.But i need all the parent node values into separate while loop.Just run the below query:

---------Just Declare the temp Table -------------------------------------------------

IF OBJECT_ID('tempdb.dbo.##TestTable','U')IS NOT NULL DROP TABLE ##TestTable
CREATE TABLE ##TestTable(id int,Uname nvarchar(max),Uaddress nvarchar(max))
INSERT INTO ##TestTable values (1,'abc','NY')
INSERT INTO ##TestTable values (2,'def','WD')
INSERT INTO ##TestTable values (3,'','KL')

DECLARE @XML XML
DECLARE @WhereClause nvarchar(max)
DECLARE @CountVal int
SELECT @CountVal=count(*) from ##TestTable
SET  @XML= (SELECT * FROM ##TestTable FOR XML PATH('ParentNode'), ELEMENTS XSINIL)
SELECT @XML
;with cte as
( select xr.value('fn:local-name(.)','nvarchar(max)') name, xr.value('.','nvarchar(max)') val  from @xml.nodes('//.') xq(xr)
    where xr.value('fn:local-name(.)','nvarchar(max)')<>''
)
SELECT @WhereClause= STUFF((select ' and ' + name + '='''+val+'''' from cte for xml path('')),1,4,'')
SELECT @WhereClause

WHILE (@CountVal>0)
BEGIN
    SELECT @WhereClause
    SET @CountVal=@CountVal-1
END
IF OBJECT_ID('tempdb.dbo.##TestTable','U')IS NOT NULL DROP TABLE ##TestTable

Current sample XML(in @XML):

<ParentNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><id>1</id><Uname>abc</Uname><Uaddress>NY</Uaddress></ParentNode><ParentNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><id>2</id><Uname>def</Uname><Uaddress>WD</Uaddress></ParentNode><ParentNode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><id>3</id><Uname /><Uaddress>KL</Uaddress></ParentNode>

Current the output of @WhereClause is (all into in a single @WhereClause variable):

ParentNode='1abcNY' and id='1' and Uname='abc' and Uaddress='NY' and ParentNode='2defWD' and id='2' and Uname='def' and Uaddress='WD' and ParentNode='3KL' and id='3' and Uname='' and Uaddress='KL'

But my Expected output is:

Firstly(in @WhereClause): id='1' and Uname='abc' and Uaddress='NY'
Secondly(in @WhereClause):id='2' and Uname='def' and Uaddress='WD'
Thirdly(in @WhereClause):id='3' and Uname='' and Uaddress='KL'

. .

How i get it. Thanks in advance.

Mikael Eriksson · Accepted Answer · 2012-11-20 14:53:24Z

3

Try this:

declare @WhereClause nvarchar(max)
declare @CountVal int

select @CountVal=count(*)
from ##TestTable

while @CountVal>0
begin
    select @WhereClause =
           (
             select ' and '+T.N.value('local-name(.)', 'nvarchar(max)')+'='+T.N.value('.', 'nvarchar(max)')
             from (
                    select *
                    from ##TestTable
                    where id = @CountVal
                    for xml path(''), type
                  ) as C(X)
               cross apply C.X.nodes('/*') as T(N)
             for xml path(''), type
           ).value('substring((./text())[1], 6)', 'nvarchar(max)')

    select @WhereClause
    set @CountVal=@CountVal-1
end

answered Nov 20, 2012 at 14:53

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

riad Over a year ago

Many Many Thanks. Exactly what i want.

bummi · Accepted Answer · 2012-11-20 15:33:26Z

2

seem to be late and having missunderstood, that woul habe been my approach

DECLARE @XML XML
DECLARE @WhereClause nvarchar(max)
DECLARE @CountVal int
SELECT @CountVal=count(*) from ##TestTable
SET  @XML= (SELECT * FROM ##TestTable FOR XML PATH('ParentNode'), ELEMENTS XSINIL)
SELECT @XML
;with cte as
( select xr.value('fn:local-name(.)','nvarchar(max)') name, xr.value('.','nvarchar(max)') val  from @xml.nodes('//.') xq(xr)
    where xr.value('fn:local-name(.)','nvarchar(max)')<>'' and xr.value('fn:local-name(.)','nvarchar(max)')<>'ParentNode'
)



SELECT @WhereClause= SubString((select Case when Name ='id'  then   CHAR(10) +''+ name + '='''+val+'''' else  ' and ' + name + '='''+val+'''' end from cte for xml path('')),2,100000000)
Print  @WhereClause

answered Nov 20, 2012 at 15:33

bummi

27.4k14 gold badges67 silver badges105 bronze badges

1 Comment

riad Over a year ago

Thanks @bummi. your answer is very helpful, as previous.

Collectives™ on Stack Overflow

SQL- fetch value from XML parent node (separately)

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related