SQL XML 操作



实例一：
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