SQL2005 Tips XML 处理例子(1)

USE Test

--Create 2 tables as an example

CREATE TABLE ExampleTable

(

[ID] int PRIMARY KEY

,[Name] nvarchar(256)

)

CREATE TABLE ExampleTable2

(

[ID] int PRIMARY KEY

,[Name] nvarchar(256)

)

----way1

SELECT

*

FROM

sys.objects [table]

WHERE

[Name] LIKE 'ExampleTable%'

FOR XML AUTO, ROOT('tables')

 

--way2

SELECT[name] AS [@name], object_id AS [@object_id],

* FROM sys.objects

WHERE [Name] LIKE 'ExampleTable%'

FOR XML PATH('table'), ROOT('tables')

 

--SELECT * FROM sys.objects WHERE [Name] LIKE 'ExampleTable%'

 

--Way3

-------------------------------------

--Nested Results using FOR XML PATH--

-------------------------------------

-------------------------------------

--Nested Results using FOR XML PATH--

-------------------------------------

 

SELECT

t.[name] AS [@name]

,t.object_id AS [@object_id]

,t.[create_date] AS [date/@cdate]

,t.[modify_date] AS [date/mdate]

,CAST('<mydata>some random xml</mydata>' AS xml) [*]

,(

     SELECT

     c.[name] AS [@name]

     ,c.[column_id] AS [@column_id]

     ,c.[is_nullable] AS [@allows_nulls]

     ,ty.[name] AS [@datatype]

     FROM sys.columns c

     INNER JOIN sys.types ty ON c.[user_type_id] = ty.[user_type_id]

     WHERE   t.[object_id] = c.[object_id]

     FOR XML PATH('columns'), TYPE

)

FROM sys.objects t

WHERE [Name] LIKE 'ExampleTable%'

FOR XML PATH('table'), ROOT('tables')

 

 

SELECT  cdate, * FROM syscolumns WHERE id=1077578877

SELECT * FROM sysobjects WHERE id=1077578877

SELECT * FROM sys.objects WHERE object_id=1077578877

 

 

--------------------

--Set Xml Variable--

--------------------

/*

In 2000, it wasn't possible to set any variable to the result of a query

*/

DECLARE @xml xml

SELECT @xml =

(

SELECT TOP 3

[Name]

FROM sys.objects

FOR XML AUTO

)

SELECT @xml

 

 

 

/*

The query method allows reformatting of a document into other formats

It could be considered a little like XSLT

*/

DECLARE @doc xml

SET @doc =

'<people>

<person>Martin</person>

<person>Simon</person>

<person>Mark</person>

</people>'

SELECT @doc.query('

for $p in /people/person/text()

return

<root>

     <name>

     {$p}

     </name>

</root>

') AS queryresults

 

 

/*

The value method allows a single value to be extracted from an xml document

*/

DECLARE @doc xml, @count int,@name1 nvarchar(20)

DECLARE @maxName AS nvarchar(20)

 

SET @doc =

     '<people>

          <person>Martin</person>

          <person>Simon</person>

          <person>Mark</person>

     </people>'

SELECT @count = @doc.value('count(/people/person)', 'int')

SELECT @name1 = @doc.value('(/people/person/text())[2]','nvarchar(20)')

 

SELECT @count AS [count]

SELECT @name1 AS Name1

 

 

----------------------

--Using sql:variable--

----------------------

/*

It is not possible to construct a custom xquery string

However, it is possible to pass in a sql variables as parameters

to the XQuery statement.

NOTE: SQL must be sure that a single value will be returned,

hence the final [1], even though the statement

/people/person[sql:variable("@pos")]/text()

would only ever return 1 value

*/

DECLARE @doc xml, @pos int

SET @doc =

'<people>

<person>Martin</person>

<person>Simon</person>

<person>Mark</person>

</people>'

SELECT @pos = 2

SELECT @doc.value('(/people/person[sql:variable("@pos")]/text())[1]', 'nvarchar(256)')AS SecondName

SELECT @doc.value('(//people/person/text())[2]','nvarchar(20)')

 

 

/*

.nodes can be used to shred xml into a table

In 2000 we had to use OPENXML

*/

 

DECLARE @doc xml

SET @doc =

'<people>

<person>Martin</person>

<person>Simon</person>

<person>Mark</person>

</people>'

SELECT

PeopleTable.PersonColumn.query('text()') AS [Name]

FROM

@doc.nodes('people/person') AS PeopleTable(PersonColumn);

 

 

 

DECLARE @doc xml

SET @doc =

'<people>

     <person id=''1''>

          <name>benny</name>

          <name>aa</name>

          <phone>49313</phone>

          <address/>

     </person>

     <person id=''2''>

          <name>skype</name>

          <phone>138</phone>

          <address>park</address>

     </person>

</people>'

/*

     ID Name  phone address

     1 benny  19313 ''

     2 skype  138 park

*/

 

SELECT

     T.c.value('@id','char(1)') AS Id

     ,T.c.value('name[2]','nvarchar(20)')

     ,T.c.value('phone[1]','nvarchar(10)')

FROM @doc.nodes('/people/person') T(c)

WHERE T.c.value('@id','int') = 1

AND T.c.value('phone[1]','nvarchar(10)')='49313'

 

 

SELECT

PeopleTable.PersonColumn.query('/name/text()') AS [Name]

FROM @doc.nodes('/people/person') AS PeopleTable(PersonColumn)

WHERE PeopleTable.PersonColumn.value('@id','int') = 1

 

--use sql variables

DECLARE @doc xml

SET @doc =

'<people>

<person>Martin</person>

<person id=''1''>Simon</person>

<person>Mark</person>

</people>'

SELECT @doc.value()

 

 

--其实XML相对路径的问题，请确认是Namespace不确定还是XML节点路径都不确定(如果是这样，那么设计的XML稳定性有待增强)。

--下面的Demo，是在解析多个Namespace的XML节点，但是路径至少可以确定的：

 

     DECLARE @XML xml

     SET @XML=N'

     <Publish xmlns="http://soa.newegg.com/SOA/USA/InfrastructureService/V10/PubSubService">

           <UserData xmlns="">

                 <SessionID>0xABde12345</SessionID>

                   <OrderNumber>1000</OrderNumber>

             <Node>

                   <SessionID>0xABde12345</SessionID>

                   <OrderNumber>102365</OrderNumber>

             </Node>

           </UserData>

     </Publish>'

 

     --这个是相对路径的解析方式,这种解析方式很容易出现解析到的数据不是我们想要的,比如,我们先想要的Node节点下的SessionID和OrderNumber

     --;WITH XMLNAMESPACES (DEFAULT 'http://soa.newegg.com/SOA/USA/InfrastructureService/V10/PubSubService')

     SELECT @XML.value('(//SessionID/text())[1]','CHAR(15)')

                 ,@XML.value('(//OrderNumber/text())[1]','int')

 

 

--虽然XML的Namespace不确定，但是，我们可以确定XML的路径，这样子避免解析到其他节点的数据。

SELECT @XML.value('(/*:Publish/*:UserData/*:Node/*:SessionID/text())[1]','CHAR(15)')

          ,@XML.value('(/*:Publish/*:UserData/*:Node/*:OrderNumber/text())[1]','int')