foncnushapel
New Member
I am new in T-SQL and XQuery.I have XML column in DB with structure like this\[code\] <GPC xmlns:x="http://www.w3.org/2001/XMLSchema" xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="..."> <GP> <N>Amount1</N> <V i:type="X_AMT"> <AMT>100001</AMT> <X_CODE>Dollar</X_CODE> </V> </GP> <GP> <N>Amount2</N> <V i:type="X_AMT"> <AMT>0</AMT> <X_CODE /> </V> </GP> <GP> <N>Amount3</N> <V i:type="X_AMT"> <AMT>100001</AMT> <X_CODE>Dollar</X_CODE> </V> </GP> <GP> <N>Amount4</N> <V i:type="X_AMT"> <AMT>0</AMT> <X_CODE /> </V> </GP> <GP> <N>Amount5</N> <V i:type="X_AMT"> <AMT>100001</AMT> <X_CODE>Dollar</X_CODE> </V> </GP> <GP> **<N>NeededAmount</N>** <V i:type="Y"> <DETAILS> <REFERENCE> <N>**ReferenceName1**</N> <OId>111111</OId> </REFERENCE> </DETAILS> <DETAILS> <REFERENCE> <N>**ReferenceName2**</N> <OId>22222</OId> </REFERENCE> </DETAILS> </V> </GP> ... </GPC>\[/code\]This is query in SQL Server that i am using. The Query returns only one name - Name1.But there is two names, and there can be a 100 names, and i want to get that names.\[code\] SELECT v.Content.query(N'declare default element namespace "..."; for $i in (GPC/GP) where ($i/N[1] eq "NeededAmount") return ($i)').value('declare default element namespace "..."; (GP/V/DETAILS/REFERENCE/N)[1]', 'nvarchar(max)') AS NeededName FROM DB1.protected.WorkItem as v where v.Id = 1111\[/code\]My qustion is: How can i modify this to get all names in document? Can someone help me?Thanks in advance.