SQL SERVER XML learning Summary

SQL SERVER XML learning Summary

The latest project task is to implement a data synchronization function, and technical preparations are being made these days, mainly using Microsoft's Service Broker technology. While familiarizing yourself with this technology, I also used some knowledge about SQL server xml. I learned some about SQL xml at home these two days. Below is a summary of some of the learning processes, I hope to help you.

The main reference in the learning process (http://blog.csdn.net/beirut/article/details/8154102 ).

Test Data

Use TestDB

Go

Create Table TbFroXML (id uniqueidentifier primary key, name nvarchar (50), [type] nvarchar (50 ))

Go

 

Insert into TbFroXML values (NEWID (), 'bben', 'mg ')

Insert into TbFroXML values (NEWID (), 'bb ', 'mg ')

Insert into TbFroXML values (NEWID (), 'live', 'mg ')

Insert into TbFroXML values (NEWID (), 'Today ', 'nmg ')

Insert into TbFroXML values (NEWID (), 'Tomorrow ', 'nmg ')

Insert into TbFroXML values (NEWID (), 'futuristic ', 'nmg ')

Insert into TbFroXML (id, name) values (newid (), 'dump ')

Go

1. For Xml Path

 

-- No name column. A corresponding row Element is generated for each row in the row set. Format: <row> 1 </row>

Select 1 for xml path

 

-- Production format <row> <name> XXX </name> </row>

Select name from TbFroXML for xml path

 

-- Remove the name tag and only the row tag is left. format: <row> BBen </row> <row> BB> </row> <row> Olive </row> ....

Select name + ''from TbFroXML for xml path

 

-- Only generate the data of the columns to be queried and remove the row Element. Format: BB, tomorrow, tomorrow, BBenOlive

Select name + ''from TbFroXML for xml path ('')

 

-- Format: <row id = 'xxxxxxx'> <name> BBen </name> </row>

Select id as '@ id', name from TbFroXML for xml path

 

-- Mark the row with NMG. Format: <NMG> <name> BBen </name> </NMG> <name> future </name> </NMG>

Select name from TbFroXML for xml path ('nmg ')

 

-- Use NMG as the row mark and Mg as the inner mark. format: <NMG> <MG> BB </MG> <NMG> <MG> future </MG> </NMG>

Select name as MG from TbFroXML for xml path ('nmg ')

 

-- Format: <row> <BBen> <Olive> BB </Olive> </BBen> </row>

Select name as 'bben/olive' from TbFroXML for xml path

 

-- Generation format: <row id = '******'> BB> </row> <row id = '********'> future </row>

Select id as '@ id', name as' * 'from TbFroXML for xml path

 

-- If the path specified as the column name is data (), the value is processed as an atomic value in the generated XML file,

-- Generation format: <row id = '*****' name = "BB"> <name> BB </name> MG </row>

Select id as '@ id', name as' @ name', name, [TYPE] as 'data () 'from TbFroXML for xml path

-- By default, the Null value in the column is mapped to "the corresponding attribute, node, or element is missing, use the ELEMENTS command to request element-centric XML and specify XSINIL to request NULL values to add ELEMENTS. Format: <row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance" id = "7B890E0B-C470-4E93-89A4-3041B70E8DF3"> <xx> <null xsi: nil = "true"/> <name> stupid </name> <type xsi: nil = "true"/> </xx> </row>

Select id as '@ id', null as 'xx/null', name as 'xx/name', [TYPE] as 'xx/type' from TbFroXML for xml path, elements xsinil

 

-- ROOT ('oo '), -- specify to add a single top-level element to the generated XML

Select id as '@ id', name from TbFroXML for xml path, root ('oo ')

 

2. For Xml Row/Auto mode

 

-- Auto mode. Format: <TbFroXML id = "41D6A175-C079-4861-9C75-2EE48A62C3BC" name = "BB" type = "MG"/>

Select * from TbFroXML for xml auto

 

The -- elements option maps each column value to the child element of the <row> element. format: <TbFroXML> <id> 41D6A175-C079-4861-9C75-2EE48A62C3BC </id> <name> BB </name> <type> MG </type> </TbFroXML>

Select * from TbFroXML for xml auto, elements

 

-- Row mode: Format: <row> <id> 7B890E0B-C470-4E93-89A4-3041B70E8DF3 </id> <name> stupid </name> <type xsi: nil = "true"/> </row>

Select * from TbFroXML for xml raw, elements

 

-- Elements option. When the value is XSINIL, the null column in The result set is mapped to the element of the xsi: nil = true attribute. Format: <row xmlns: xsi = "http://www.w3.org/2001/XMLSchema-instance"> <id> 41D6A175-C079-4861-9C75-2EE48A62C3BC </id> <name> BB </name> <type> MG </type> </row>

Select * from TbFroXML for xml raw, elements xsinil

 

-- Xmldata: Result Request Architecture

Select * from TbFroXML for xml raw, xmldata

 

-- Xmlschema: Result Request Architecture

Select * from TbFroXML for xml raw, xmlschema

3. For XML Explicit

Select 1 as Tag,

NULL as Parent,

3 as [node! 1] -- [node! 1] indicates the root node, and node indicates the node level of the node hierarchy.

For xml explicit

 

Select 1 as Tag,

Null as Parent,

Null as [root! 1],

Null as [node! 2! Id! Element] -- [node! 2! Id! ELEMENT] node indicates the node name; 2 indicates the node level; id indicates the ELEMENT name ;. the ELEMENT option indicates that the <id> ELEMENT sub-level is added to the <node> ELEMENT, rather than the attribute.

Union all

Select 2 as Tag,

1 as Parent,

Null,

Id

From TbFroXML where id! = Null for xml explicit

4. XPath

XPath uses path expressions to select nodes or node sets in XML documents. Nodes are selected by following the path or step (steps.

4.1 select a node

The most useful path expressions are listed below:

Expression	Description
Nodename	Select all child nodes of the node.
/	Select from the root node.
//	Select the nodes in the document from the current node that matches the selected node, regardless of their location.
.	Select the current node.
..	Select the parent node of the current node.
@	Select attributes.

4.2 Predicates)

It is used to find a specific node or a node that contains a specified value.

The predicates are embedded in square brackets.

Example:/root/Info/user [1]

4.3 select an unknown Node

The XPath wildcard can be used to select unknown XML elements.

Wildcard	Description
*	Match any element node.
@*	Match any attribute node.
Node ()	Match any type of nodes.

Example: All element nodes under the/root/info/* info Node

/Root/info/@ * All attribute nodes under the info Node

// User [@ *] All user nodes with attributes

4.4 select several paths

You can select several paths by using the "|" operator in the path expression.

Example: // User |/root/Info/@ id

5. Basic query of XQuery5.1

DECLARE @ dataSource XML

SET @ dataSource ='

<Root>

<Info id = "1">

<User uid = "001">

<Name> stupid </name>

<Type> MG </type>

</User>

</Info>

<Info id = "2">

<User uid = "002">

<Name> Olive </name>

<Type> MG </type>

</User>

</Info>

<Info id = "3">

<User uid = "003">

<Name> Mo Yao </name>

<Type> NMG </type>

</User>

</Info>

</Root>'

-- Get all root subnodes

Select @ dataSource. query ('.')

Select @ dataSource. query ('root ')

Select @ dataSource. query ('/root ')

 

---- Get all info Nodes

Select @ dataSource. query ('// info ')

 

---- Get the name node of the user node under all info Nodes

Select @ dataSource. query ('// info/user/name ')

 

---- Get all Info Nodes with uid> 1

Select @ dataSource. query ('/root/info [./user [@ uid> 1]')

If then else expression

Select @ dataSource. query ('If (1 = 3) then/root/info [. /user [uid = 1] else/root/info [. /user [name = "Olive"] ')

 

-- The predicate in the path expression step to obtain the name of the user node under the first info Node

Select @ dataSource. query ('/root/info [1]/user/name ')

 

-- Aggregate functions

Select @ dataSource. query ('count (/root/info [./user [type = "MG"]) ')

 

-- FLWOR Syntax: For, Let, Where, Order by, Return

Select @ dataSource. query ('<result> {for $ li in/root/info/user/name [1] return string ($ li)} </result> ')

 

Select @ dataSource. query ('<result> {for $ li in/root/info, $ lii in $ li/user/name [1] return string ($ lii )} </result> ')

 

Select @ dataSource. query ('<result> {for $ li in/root/info/user order by $ li/@ uid descending return $ li} </result> ')

 

Select @ dataSource. query ('<result> {for $ li in/root/info/user order by local-name ($ li) return $ li} </result> ')

5.2 Value ()

-- Value () gets the value of a node or its attribute and assigns it to the nvarchar type.

Select @ dataSource. value ('(/root/info/user [1]/name) [1]', 'nvarchar (20 )')

5.3 Exist ()

Select @ dataSource. exist ('/root/info/user/name [text () [1] = "stupid"]')

 

-- Convert the node attribute of the date type to the date type and compare it with the corresponding value

Declare @ date xml

Set @ date = '<root date = "2013-10-07 7"/>'

-- Exist ()

Select @ date. exist ('/root [(@ date cast as xs: date ?) Eq xs: date ("2013-10-07")] ')

 

-- Convert the value of a node of the date type to the date type and compare it with the corresponding value. date [text () [1] cast xs: date ?, Convert node value to date type

Declare @ date1 xml

Set @ date1 = '<root> <date> 2013-10-07 </date> </root>'

Select @ date1.exist ('/root/date [(text () [1] cast as xs: date ?) Eq xs: date ("2013-10-07")] ')

 

-- Obtain the value of any attribute: SQL: variable ("@ attriname ")

Declare @ attr nvarchar (20) = 'uid'

If @ dataSource. exist ('/root/info/user/@ * [local-name () = SQL: variable ("@ attr")]')> = 1

Select 'OK'

Else select @ dataSource. query ('/root/info/user/name [local-name () = "stupid"]')

 

 

5.4 Nodes ()

-- Nodes () method, which splits an xquery expression into multiple rows

Select T. c. query ('.') as result from @ dataSource. nodes ('/root/info') as T (c)

Select T. c. query ('.') as result from @ dataSource. nodes ('/root/info/user') as T (c)

Select T. c. value ('(@ uid) [1]', 'varchar (10) ') as id,

T. c. value ('(./name) [1]', 'nvarchar (20) ') as name,

T. c. value ('(./type) [1]', 'nvarchar (20) ') as [type]

From @ dataSource. nodes ('/root/info/user') T (c)

-- The first value method gets the value of Node B: cec, and the second value method gets the value of Node B: c

 

Declare @ xml = '<root> <a> <B> c <d> e </d> c </B> </a> </root>'

Select @ xml. value ('(/root/a/B) [1]', 'nvarchar (20) '), @ xml. value ('(/root/a/B/text () [1]', 'nvarchar (20 )')

 

-- Combined Use

Declare @ xml = '<root> <info id = "001" name = "Olive" type = "MG"/> </root>'

Declare @ pos int = 2

Select @ xml. value ('local-name (/root/info/@ * [position () = SQL: variable ("@ pos")]) [1]) ', 'nvarchar (20 )')

 

-- SQL: column: Merge common data columns with Xml data

Declare @ tb table (id int, data xml)

Insert into @ tb (id, data) select 1, '<root> <info> <name> Benben </name> <type> MG </type> </info> </root>'

Select id, data = data. query ('<root> <info> <id> {SQL: column ("id ")} </id> {/root/info/name} {/root/info/type} </info> </root> ') from @ tb

 

--- Contains: fuzzy query contains (., 'xx ')

Select t. c. query ('. ') from @ dataSource. nodes ('/root/info/user [. /name [contains (., "Stupid")] ') t (c)

5.5 Modify () 5.5.1 Insert

-- Add a subnode insert into under a node. as first/as last specifies the node Insertion Location.

Set @ dataSource. modify ('insert <sex> F </sex> as last into (/root/info/user) [1] ')

Select @ dataSource

 

-- Add a peer node to a node, and add a peer node to before/after

Set @ dataSource. modify ('insert <UID> A1 </UID> before (/root/info/user/name) [1] ')

Select @ dataSource

 

-- Insert attributes

Declare @ a int = 111

Set @ dataSource. modify ('insert (attribute a {SQL: variable ("@ ")},

Attribute B {". 3 "})

Into (/root/info/user [@ uid = 001]) [1] ')

Select @ dataSource

 

5.5.2 Delete

-- Delete attributes

Set @ dataSource. modify ('delete/root/info/user/@ uid ')

Select @ dataSource

 

-- Delete a node

Set @ dataSource. modify ('delete/root/info/user/type ')

Select @ dataSource

-- Delete node content

Set @ dataSource. modify ('delete/root/info/user/type/text ()')

Select @ dataSource

 

-- Delete all nodes with empty properties

Set @ dataSource. modify ('delete // * [empty (./*)] ')

Select @ dataSource

 

5.5.3 Replace

-- Modify node Value

Set @ dataSource. modify ('replace value of (/root/info/user/name/text () [1] with "stupid "')

Select @ dataSource

 

-- Modify attribute values

Set @ dataSource. modify ('replace value of (/root/info/user/@ uid) [1] with "0001 "')

Select @ dataSource