实例一:
DECLARE @x xml
SET @x='<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row id="2"><name>moe</name></row>
<row id="3" />
</Root>'
SELECT T.c.value('(@id)', 'int') AS result
FROM @x.nodes('//Root/row') as T(c)
GO
实例二:
DECLARE @XML AS XML;
SET @XML = CAST('<Session id="ID969138672" realTimeID="4300815712">
<VarValues>
<varValue id="ID123" source="Internal" name="DisconnectedBy">VisitorClosedWindow</varValue>
<varValue id="ID1234" source="PreChat" name="email">1234@mail.ru</varValue>
</VarValues>
</Session>
' AS XML)
SELECT
xmlData.Col.value('@id','varchar(max)') as id
,xmlData.Col.value('@source','varchar(max)') as source
,xmlData.Col.value('@name','varchar(max)') as name
,xmlData.Col.value('.','varchar(max)') as value
FROM @XML.nodes('//Session/VarValues/varValue') xmlData(Col);
实例三:
DECLARE @x xml
SET @x='<Root>
<row id="1"><name>Larry</name><oflw>some text</oflw></row>
<row1 id="2"><name>moe</name></row1>
<row2 id="3" />
</Root>'
SELECT T.c.query('//Root/row') AS result,
T.c.value('(./row1/name)[1]','nvarchar(50)') as name,
T.c.value('(./row2/@id)[1]','int') as id
FROM @x.nodes('//Root') as T(c)
GO