How to get all the values of child elements of an xml file. SQL server 2008
Here is how my XML looks like. I am trying to query XML in SQL Server 2008. I want to display all the claims based on ID.
<clue xmlns="http://cp.com开发者_开发知识库/rules/client">
<claim unit_number="1" number="0000007517" id="S1"
sequence_number="1">
<scope_of_claim>Full scope</scope_of_claim>
</claim>
<claim unit_number="1" number="0000007518" id="S1"
sequence_number="2">
<scope_of_claim>Full scope</scope_of_claim>
</claim>
</clue>
My Query :
The below query gives me the values for the first claim only.
;WITH XMLNAMESPACES (DEFAULT 'http://cp.com/rules/client')
select xmldocument.value('(//claim/@number)[1]','varchar(20)') as ClaimNumber,
xmldocument.value('(//claim/scope_of_claim)[1]','varchar(20)') as Scope
.....
from clue.xml
How can i get all the claims?
;WITH XMLNAMESPACES (DEFAULT 'http://cp.com/rules/client')
SELECT xmlid,
ClaimNumber,
Scope
FROM clue.xml
OUTER APPLY
(SELECT
tbl.col.value('(@number)[1]', 'varchar(20)') AS ClaimNumber,
tbl.col.value('(scope_of_claim)[1]', 'varchar(20)') AS Scope
FROM xmldocument.nodes('//claim') AS tbl(col)) x
精彩评论