[转]T-SQL操作XML,用于自己慢慢吸收

USE tempdb;
GO


--First
CREATE TABLE #T (ID INT PRIMARY KEY IDENTITY(1,1),Col1 XML);
GO

INSERT INTO #T SELECT N'
<books>
    <book id="1" />
    <book id="2" />
</books>
'

SELECT * FROM #T


INSERT INTO #T SELECT N'
<books>
    <book id="1" />
    <book id="2" />
    <book>
</books>
'
GO

--Second

CREATE XML SCHEMA COLLECTION [dbo].[BookSchemaCollection]
AS
N'
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="books">
    <xs:complexType>
      <xs:sequence>
        <xs:element maxOccurs="unbounded" name="book">
          <xs:complexType>
            <xs:attribute name="id" type="xs:unsignedByte" use="required" />
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>'

SELECT * FROM sys.xml_schema_collections

SELECT * FROM sys.xml_schema_collections A INNER JOIN sys.schemas B ON A.schema_id=B.schema_id

SELECT XML_SCHEMA_NAMESPACE('dbo','BookSchemaCollection')


DECLARE @xml XML (dbo.BookSchemaCollection)

SET @xml=N'<books></books>'
SET @xml=N'<books><book /></books>'
SET @xml=N'<books><book id="1"/></books>'
SET @xml=N'<books><book id="1"/><other></other></books>'
GO


--Third
DECLARE @xml XML;
SET @xml=N'
<books>
  <book id="1" />
  <book id="2" />
  <book />
</books>
'
--返回所有的book节点
SELECT @xml.query('/books');

--返回当前上下文节点
SELECT @xml.query('.')

--返回当前上级节点
SELECT @xml.query('/books/book/..')

--查询ID为1的book节点
SELECT @xml.query('/books/book[@id="1"]')

--查询book节点集合中的第2个
SELECT @xml.query('/books/book[2]');

--查询含有id属性的book节点
SELECT @xml.query('/books/book[@id]')

--查询所有book节点
SELECT @xml.query('//book')
GO

DECLARE @xml XML;
SET @xml=N'
<lk:books xmlns:lk="http://www.mm2vv.cn">
    <lk:book id="1" />
    <lk:book id="2" />
    <lk:book id="3" />
</lk:books>
'

--查询不到,没带命名空间
SELECT @xml.query('/books/book');

--内嵌方式定义命名空间
SELECT @xml.query('declare namespace lk="http://www.mm2vv.cn";
/lk:books/lk:book
');

--外部声明
WITH XMLNAMESPACES
(
'http://www.mm2vv.cn' AS lk
)
SELECT @xml.query('/lk:books/lk:book');
GO

--Fourth
DECLARE @xml XML;
SET @xml=N'
<lk:books xmlns:lk="http://www.mm2vv.cn">
    <lk:book id="1">book1</lk:book>
    <lk:book id="2">book2</lk:book>
    <lk:book id="3">book3</lk:book>
</lk:books>
'
--取第一个book节点的值
SELECT @xml.value('declare namespace lk="http://www.mm2vv.cn";
(/lk:books/lk:book)[1]','varchar(20)');

--取属性值
SELECT @xml.value('declare namespace lk="http://www.mm2vv.cn";
(/lk:books/lk:book/@id)[1]','int');
GO

--Fifth
DECLARE @xml XML;
SET @xml=N'
<lk:books xmlns:lk="http://www.mm2vv.cn">
    <lk:book id="1">book1</lk:book>
    <lk:book id="2">book2</lk:book>
    <lk:book id="3">book3</lk:book>
</lk:books>
'
--判断ID=3的book节点是否存在
SELECT @xml.exist('declare namespace lk="http://www.mm2vv.cn";/lk:books/lk:book[@id="3"]');

DECLARE @x xml
DECLARE @f bit
SET @x = '<Somedate>2002-01-01Z</Somedate>'
SET @f = @x.exist('/Somedate[(text()[1] cast as xs:date ?) = xs:date("2002-01-01Z") ]')
SELECT @f
GO

--Sixth
DECLARE @xml XML
SET @xml=N'
<books>
  <book id="1">book1</book>
  <book id="2">book2</book>
  <book id="3">book3</book>
</books>
';

--nodes 返回未命名的行集,所以使用AS T(x) 做一个表T含有一个类型为XML的x列的 T(x),然后使用query查询获取当前节点值
SELECT T.x.query('./text()') FROM @xml.nodes('/books/book') AS T(x)

--不能直接返回T.x
--SELECT T.x FROM @xml.nodes('/books/book') AS T(x)

--返回属性值
SELECT T.x.value('./@id','int') FROM @xml.nodes('/books/book') AS T(x)

--nodes返回T1表以后,使用OUTER APPLY 继续拆分name节点,最后判断row节点下是否含有name,然后查询
DECLARE @x xml        
SET @x='       
<Root>       
    <row id="1"><name>Larry</name><oflw>some text</oflw></row>       
    <row id="2"><name>Joe</name></row>       
    <row id="3" />       
</Root>'       
SELECT T1.rows.value('@id','int') as id,T1.rows.query('.')       
FROM @x.nodes('/Root/row') T1(rows)       
OUTER APPLY T1.rows.nodes('./name') as T2(names)       
WHERE T2.names IS NOT NULL        
GO       

--Seventh
DECLARE @T TABLE(ID INT PRIMARY KEY IDENTITY(1,1),Col1 XML)
INSERT INTO @T SELECT N'
<books>
    <book id="1" />
</books>'
UNION ALL SELECT N'
<books>
    <book id="2" />
</books>
'
--插入到自定义节点中book 的id属性,调用的是ID主键
SELECT Col1.query('<book id="{ sql:column("A.ID") }" />') FROM @T A

DECLARE @I INT
SET @I='123123';

SELECT Col1.query('<book id="{ sql:variable("@I") }" />') FROM @T A

Download