SQL Server XMLデータ解析
20866 ワード
--5. XML
-- XML EMAIL
DECLARE @x XML
SELECT @x = '
<People>
<dongsheng>
<Info Name="Email">[email protected]</Info>
<Info Name="Phone">678945546</Info>
<Info Name="qq">36575</Info>
</dongsheng>
</People>'
-- 1
SELECT @x.value('data(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 2
SELECT @x.value('(/People/dongsheng/Info[@Name="Email"])[1]', 'varchar(30)')
-- 3
SELECT
C.value('.','varchar(30)')
FROM @x.nodes('/People/dongsheng/Info[@Name="Email"]') T(C)
-- 4
SELECT
C.value('(Info[@Name="Email"])[1]','varchar(30)')
FROM @x.nodes('/People/dongsheng') T(C)
-- 5
SELECT
C.value('(dongsheng/Info[@Name="Email"])[1]','varchar(30)')
FROM @x.nodes('/People') T(C)
-- 6
SELECT
C.value('.','varchar(30)')
FROM @x.nodes('/People/dongsheng/Info') T(C)
WHERE C.value('(.[@Name="Email"])[1]','varchar(30)') IS NOT NULL
-- 7
SELECT
C.value('.','varchar(30)')
FROM @x.nodes('/People/dongsheng/Info') T(C)
WHERE C.exist('(.[@Name="Email"])[1]') = 1
--6.Reading values from an XML variable
DECLARE @x XML
SELECT @x =
'<Peoples>
<People Name="tudou" Sex=" " />
<People Name="choushuigou" Sex=" "/>
<People Name="dongsheng" Sex=" " />
</Peoples>'
SELECT
v.value('@Name[1]','VARCHAR(20)') AS Name,
v.value('@Sex[1]','VARCHAR(20)') AS Sex
FROM @x.nodes('/Peoples/People') x(v)
--7.
DECLARE @x XML
SELECT @x = '
<Employees>
<Employee id="1234" dept="IT" type=" ">
<Info NAME="dongsheng" SEX=" " QQ="5454545454"/>
</Employee>
<Employee id="5656" dept="IT" type=" ">
<Info NAME=" " SEX=" " QQ="5345454554"/>
</Employee>
<Employee id="3242" dept=" " type=" ">
<Info NAME="choushuigou" SEX=" " QQ="54543545"/>
</Employee>
</Employees>'
-- dept IT
-- 1
SELECT
C.value('@NAME[1]','VARCHAR(10)') AS NAME,
C.value('@SEX[1]','VARCHAR(10)') AS SEX,
C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('/Employees/Employee[@dept="IT"]/Info') T(C)
/*
NAME SEX QQ
---------- ---------- --------------------
dongsheng 5454545454
5345454554
*/
-- 2
SELECT
C.value('@NAME[1]','VARCHAR(10)') AS NAME,
C.value('@SEX[1]','VARCHAR(10)') AS SEX,
C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('//Employee[@dept="IT"]/*') T(C)
/*
NAME SEX QQ
---------- ---------- --------------------
dongsheng 5454545454
5345454554
*/
-- IT type Permanent
SELECT
C.value('@NAME[1]','VARCHAR(10)') AS NAME,
C.value('@SEX[1]','VARCHAR(10)') AS SEX,
C.value('@QQ[1]','VARCHAR(20)') AS QQ
FROM @x.nodes('//Employee[@dept="IT"][@type=" "]/*') T(C)
/*
NAME SEX QQ
---------- ---------- --------------------
dongsheng 5454545454
*/
--12. XML
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME> </NAME>
<SEX> </SEX>
<QQ>5345454554</QQ>
</People>
</Peoples>'
SET @x.modify('
delete (/Peoples/People/SEX)[1]'
)
SELECT @x
/*
<Peoples>
<People>
<NAME> </NAME>
<QQ>5345454554</QQ>
</People>
</Peoples>
*/
--19.
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
<QQ>423545</QQ>
</People>
<People>
<NAME> </NAME>
<SEX> </SEX>
<QQ>123133</QQ>
</People>
<People>
<NAME>choushuigou</NAME>
<SEX> </SEX>
<QQ>54543545</QQ>
</People>
</Peoples>
'
DECLARE @ElementName VARCHAR(20)
SELECT @ElementName = 'NAME'
SELECT c.value('.','VARCHAR(20)') AS NAME
FROM @x.nodes('/Peoples/People/*[local-name()=sql:variable("@ElementName")]') T(C)
/*
NAME
--------------------
dongsheng
choushuigou
*/
--20
--
DECLARE @x1 XML
SELECT @x1 = '<People>dongsheng</People>'
SELECT @x1.value('(/*/text())[1]','VARCHAR(20)') AS People -- *
/*
People
--------------------
dongsheng
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
<QQ>423545</QQ>
</People>'
SELECT
@x.value('(/*/*/text())[1]','VARCHAR(20)') AS NAME
/*
NAME
--------------------
dongsheng
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
<QQ>423545</QQ>
</People>'
SELECT
@x.value('(/*/*/text())[2]','VARCHAR(20)') AS SEX
/*
SEX
--------------------
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
<QQ>423545</QQ>
</People>'
SELECT
C.value('.','VARCHAR(20)') AS value
FROM @x.nodes('/*/*') T(C)
/*
value
--------------------
dongsheng
423545
*/
--21.
DECLARE @x XML
SELECT @x = '<People>dongsheng</People>'
SELECT
@x.value('local-name(/*[1])','VARCHAR(20)') AS ElementName
/*
ElementName
--------------------
People
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
</People>'
SELECT
@x.value('local-name((/*/*)[1])','VARCHAR(20)') AS ElementName,
@x.value('(/*/*/text())[1]','VARCHAR(20)') AS ElementValue
/*
ElementName ElementValue
-------------------- --------------------
NAME dongsheng
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
</People>'
SELECT
@x.value('local-name((/*/*)[2])','VARCHAR(20)') AS ElementName,
@x.value('(/*/*/text())[2]','VARCHAR(20)') AS ElementValue
/*
ElementName ElementValue
-------------------- --------------------
SEX
*/
--
DECLARE @x XML
SELECT @x = '
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
</People>'
SELECT
C.value('local-name(.)','VARCHAR(20)') AS ElementName,
C.value('.','VARCHAR(20)') AS ElementValue
FROM @x.nodes('/*/*') T(C)
/*
ElementName ElementValue
-------------------- --------------------
NAME dongsheng
SEX
*/
---22.
-- Peoples People 。
DECLARE @x XML
SELECT @x = '
<Peoples>
<People>
<NAME>dongsheng</NAME>
<SEX> </SEX>
</People>
<People>
<NAME> </NAME>
<SEX> </SEX>
</People>
<People>
<NAME>choushuigou</NAME>
<SEX> </SEX>
</People>
</Peoples>
'
SELECT @x.value('count(/Peoples/People)','INT') AS Children
/*
Children
-----------
3
*/
-- Peoples People
SELECT @x.value('count(/Peoples/People[1]/*)','INT') AS Children
/*
Children
-----------
2
*/
-- ， 。
SELECT @x.value('count(/*/*)','INT') AS ChildrenOfRoot,
@x.value('count(/*/*[1]/*)','INT') AS ChildrenOfFirstChildElement
/*
ChildrenOfRoot ChildrenOfFirstChildElement
-------------- ---------------------------
3 2
*/
--23.
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX=" " QQ="5454545454"/>
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>
</Employees>'
--
SELECT @x.value('count(/Employees/@*)','INT') AS AttributeCountOfRoot
/*
AttributeCountOfRoot
--------------------
1
*/
-- Employee
SELECT @x.value('count(/Employees/Employee[1]/@*)','INT') AS AttributeCountOfFirstElement
/*
AttributeCountOfFirstElement
----------------------------
3
*/
-- Employee
SELECT @x.value('count(/Employees/Employee[2]/@*)','INT') AS AttributeCountOfSeconfElement
/*
AttributeCountOfSeconfElement
-----------------------------
4
*/
-- *
SELECT @x.value('count(/*/@*)','INT') AS AttributeCountOfRoot
,@x.value('count(/*/*[1]/@*)','INT') AS AttributeCountOfFirstElement
,@x.value('count(/*/*[2]/@*)','INT') AS AttributeCountOfSeconfElement
/*
AttributeCountOfRoot AttributeCountOfFirstElement AttributeCountOfSeconfElement
-------------------- ---------------------------- -----------------------------
1 3 4
*/
--
SELECT C.value('count(./@*)','INT') AS AttributeCount
FROM @x.nodes('/*/*') T(C)
/*
AttributeCount
--------------
3
4
*/
--24.
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX=" " QQ="5454545454"/>
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>
</Employees>'
-- Employee
SELECT @x.value('(/Employees/Employee[1]/@*[position()=1])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
dongsheng
*/
-- Employee
SELECT @x.value('(/Employees/Employee[2]/@*[position()=4])[1]','VARCHAR(20)') AS AttValue
/*
AttValue
--------------------
13954697895
*/
--
SELECT @x.value('local-name((/Employees/Employee[1]/@*[position()=3])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
QQ
*/
--
SELECT @x.value('local-name((/Employees/Employee[2]/@*[position()=4])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
TEL
*/
--
DECLARE @Elepos INT,@Attpos INT
SELECT @Elepos=2,@Attpos = 3
SELECT @x.value('local-name((/Employees/Employee[sql:variable("@Elepos")]/@*[position()=sql:variable("@Attpos")])[1])','VARCHAR(20)') AS AttName
/*
AttName
--------------------
QQ
*/
--25. XML
DECLARE @x XML
SELECT @x = '<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>'
IF @x.exist('/Employee/@NAME') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--
DECLARE @x XML
SELECT @x = '<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>'
DECLARE @att VARCHAR(20)
SELECT @att = 'QQ'
IF @x.exist('/Employee/@*[local-name()=sql:variable("@att")]') = 1
SELECT 'Exists' AS Result
ELSE
SELECT 'Does not exist' AS Result
/*
Result
------
Exists
*/
--26.
DECLARE @x XML
SELECT @x = '<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>'
DECLARE
@cnt INT,
@totCnt INT,
@attName VARCHAR(30),
@attValue VARCHAR(30)
SELECT
@cnt = 1,
@totCnt = @x.value('count(/Employee/@*)','INT')--
-- loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@attName = @x.value(
'local-name((/Employee/@*[position()=sql:variable("@cnt")])[1])',
'VARCHAR(30)'),
@attValue = @x.value(
'(/Employee/@*[position()=sql:variable("@cnt")])[1]',
'VARCHAR(30)')
PRINT 'Attribute Position: ' + CAST(@cnt AS VARCHAR)
PRINT 'Attribute Name: ' + @attName
PRINT 'Attribute Value: ' + @attValue
PRINT ''
-- increment the counter variable
SELECT @cnt = @cnt + 1
END
/*
Attribute Position: 1
Attribute Name: NAME
Attribute Value:
Attribute Position: 2
Attribute Name: SEX
Attribute Value:
Attribute Position: 3
Attribute Name: QQ
Attribute Value: 5345454554
Attribute Position: 4
Attribute Name: TEL
Attribute Value: 13954697895
*/
--27.
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX=" " QQ="5454545454"/>
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>
</Employees>'
SELECT @x.query('(/Employees/Employee)[1]')
/*
<Employee NAME="dongsheng" SEX=" " QQ="5454545454" />
*/
SELECT @x.query('(/Employees/Employee)[position()=2]')
/*
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895" />
*/
--
DECLARE @i INT
SELECT @i = 2
SELECT @x.query('(/Employees/Employee)[sql:variable("@i")]')
--or
SELECT @x.query('(/Employees/Employee)[position()=sql:variable("@i")]')
/*
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895" />
*/
--28.
DECLARE @x XML
SELECT @x = '
<Employees dept="IT">
<Employee NAME="dongsheng" SEX=" " QQ="5454545454"/>
<Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>
</Employees>'
DECLARE
@cnt INT,
@totCnt INT,
@child XML
-- counter variables
SELECT
@cnt = 1,
@totCnt = @x.value('count(/Employees/Employee)','INT')
-- loop
WHILE @cnt <= @totCnt BEGIN
SELECT
@child = @x.query('/Employees/Employee[position()=sql:variable("@cnt")]')
PRINT 'Processing Child Element: ' + CAST(@cnt AS VARCHAR)
PRINT 'Child element: ' + CAST(@child AS VARCHAR(100))
PRINT ''
-- incremet the counter variable
SELECT @cnt = @cnt + 1
END
/*
Processing Child Element: 1
Child element: <Employee NAME="dongsheng" SEX=" " QQ="5454545454"/>
Processing Child Element: 2
Child element: <Employee NAME=" " SEX=" " QQ="5345454554" TEL="13954697895"/>SQL Server XML
1.xml.exist
XQuery ， 0，1 Null。0 ，1 ，Null
2.xml.value
XQuery ， SQL Server
3.xml.query
XQuery ， SQL Server XML
4.xml.nodes
XQuery ， XML
5.xml.modify
XQuery XML insert , update delete 。
：
declare @XMLVar xml = '
<catalog>
<book category="ITPro">
<title>Windows Step By Step</title>
<author>Bill Zack</author>
<price>49.99</price>
</book>
<book category="Developer">
<title>Developing ADO .NET</title>
<author>Andrew Brust</author>
<price>39.93</price>
</book>
<book category="ITPro">
<title>Windows Cluster Server</title>
<author>Stephen Forte</author>
<price>59.99</price>
</book>
</catalog>'
1. xml.exist
select @XMLVar.exist('/catalog/book')----- 1
select @XMLVar.exist('/catalog/book/@category')----- 1
select @XMLVar.exist('/catalog/book1')----- 0
set @XMLVar = null
select @XMLVar.exist('/catalog/book')----- null
2.xml.value
select @XMLVar.value('/catalog[1]/book[1]','varchar(MAX)')
select @XMLVar.value('/catalog[1]/book[2]/@category','varchar(MAX)')
select @XMLVar.value('/catalog[2]/book[1]','varchar(MAX)')
：
Windows Step By StepBill Zack49.99 Developer NULL
3.xml.query
select @XMLVar.query('/catalog[1]/book')
select @XMLVar.query('/catalog[1]/book[1]')
select @XMLVar.query('/catalog[1]/book[2]/author')
：
<book category="ITPro">
<title>Windows Step By Step</title>
<author>Bill Zack</author>
<price>49.99</price>
</book>
<book category="Developer">
<title>Developing ADO .NET</title>
<author>Andrew Brust</author>
<price>39.93</price>
</book>
<book category="ITPro">
<title>Windows Cluster Server</title>
<author>Stephen Forte</author>
<price>59.99</price>
</book>
<book category="ITPro">
<title>Windows Step By Step</title>
<author>Bill Zack</author>
<price>49.99</price>
</book>
<author>Andrew Brust</author>
4.xml.nodes
select T.c.query('.') as result from @XMLVar.nodes('/catalog/book') as T(c)
select T.c.query('title') as result from @XMLVar.nodes('/catalog/book') as T(c)
：
<book category="ITPro"><title>Windows Step By Step</title><author>Bill …………
<book category="Developer"><title>Developing ADO .NET</title><author>Andrew …………
<book category="ITPro"><title>Windows Cluster Server</title><author>Stephen …………
<title>Windows Step By Step</title>
<title>Developing ADO .NET</title>
<title>Windows Cluster Server</title>set ARITHABORT on
DECLARE @x XML
SELECT @x = '<Peoples>
<People>
<Email>[email protected]</Email>
<Phone>678945546</Phone>
<QQ>36575</QQ>
<Addr>36575</Addr>
</People>
</Peoples>'
-- 1
select 1001 as peopleId, p.* FROM(
SELECT
C.value('local-name(.)','VARCHAR(20)') AS attrName,
C.value('.','VARCHAR(20)') AS attrValue
FROM @x.nodes('/*/*/*') T(C) --
) as p
/*
1001 Email [email protected]
1001 Phone 678945546
1001 QQ 36575
1001 Addr 36575
*//*
XML
*/
ALTER PROCEDURE [dbo].[sp_ExportXml]
@x xml ,
@layerstr nvarchar(max)
AS
DECLARE @sql nvarchar(max)
BEGIN
set arithabort on
set @sql='select p.* FROM(
SELECT
C.value(''local-name(.)'',''VARCHAR(20)'') AS attrName,
C.value(''.'',''VARCHAR(20)'') AS attrValue
FROM @xmlParas.nodes('''+@layerstr+''') T(C)
) as p'
--print @sql
EXECUTE sp_executesql @sql, N'@xmlParas as xml',@xmlParas=@x
ENDDECLARE @x XML
SELECT @x =
'<Peoples>
<People>
<Email>[email protected]</Email>
<Phone>678945546</Phone>
<QQ>36575</QQ>
<Addr>36575</Addr>
</People>
</Peoples>'
EXECUTE sp_ExportXml @x,'/*/*/*'
/*
Email [email protected]
Phone 678945546
QQ 36575
Addr 36575
*/