覓語 譴..
蟯碁ゼ 覺.
@message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
[edit]
1 #
KEB xml朱 れ企覃 螳 .
declare
@xml xml
set @xml = '<PEXRATE>
<螻谿 殊="20190618" 谿="001" 焔螳="082426">
<覲貊>S</覲貊>
<貊>05</貊>
<豌伎> </豌伎>
<牛貊 譬襯="USD">
<覲貊>I</覲貊>
<IBM牛貊>001</IBM牛貊>
<牛覈>US.DLRS </牛覈>
<蟲螳覈>U.S.A. </蟲螳覈>
<覲伎＾>2 </覲伎＾>
<牛螻蟲覿>1</牛螻蟲覿>
<TT_BUY蠍讌覿></TT_BUY蠍讌覿>
<TT_SEL蠍讌覿></TT_SEL蠍讌覿>
<CASH_BUY蠍讌覿></CASH_BUY蠍讌覿>
<CASH_SEL蠍讌覿></CASH_SEL蠍讌覿>
<CHECK_BUY蠍讌覿></CHECK_BUY蠍讌覿>
<蟲覿貊>D</蟲覿貊>
<IBM牛貊>001</IBM牛貊>
<襷る>0117470</襷る>
<谿磯Г>0116554</谿磯Г>
<襷れ>0117331</襷れ>
<襷れ>0119790</襷れ>
<谿磯Г>0120706</谿磯Г>
<襷るГ蠍一>0118630</襷るГ蠍一>
<ル螳蟆>01186</ル螳蟆>
<覩誤一>1000000</覩誤一>
<螳襭1>0042403</螳襭1>
<螳襭12>0001178</螳襭12>
<螳襭9>0001413</螳襭9>
</牛貊>
<牛貊 譬襯="GBP">
<覲貊>I</覲貊>
<IBM牛貊>002</IBM牛貊>
<牛覈>POUND STR.</牛覈>
<蟲螳覈>ENGLAND </蟲螳覈>
<覲伎＾>2 </覲伎＾>
<牛螻蟲覿>1</牛螻蟲覿>
<TT_BUY蠍讌覿></TT_BUY蠍讌覿>
<TT_SEL蠍讌覿></TT_SEL蠍讌覿>
<CASH_BUY蠍讌覿></CASH_BUY蠍讌覿>
<CASH_SEL蠍讌覿></CASH_SEL蠍讌覿>
<CHECK_BUY蠍讌覿></CHECK_BUY蠍讌覿>
<蟲覿貊>D</蟲覿貊>
<IBM牛貊>002</IBM牛貊>
<襷る>0147245</襷る>
<谿磯Г>0145802</谿磯Г>
<襷れ>0147135</襷れ>
<襷れ>0150219</襷れ>
<谿磯Г>0151662</谿磯Г>
<襷るГ蠍一>0148732</襷るГ蠍一>
<ル螳蟆>01487</ル螳蟆>
<覩誤一>1253700</覩誤一>
<螳襭1>0027015</螳襭1>
<螳襭12>0000740</螳襭12>
<螳襭9>0000888</螳襭9>
</牛貊>
</螻谿>
</PEXRATE>
'
select
convert(date, a.nth.value('@殊', 'varchar(8)')) 殊
, concat(left(a.nth.value('@焔螳', 'varchar(8)'),2), ':', substring(a.nth.value('@焔螳', 'varchar(8)'), 3, 2), ':', right(a.nth.value('@焔螳', 'varchar(8)'),2)) 螻螳
, a.nth.value('(覲貊)[1]', 'varchar(8)') 覲貊
, a.nth.value('(貊)[1]', 'varchar(8)') 貊
, b.currency_code.value('@譬襯', 'varchar(20)') 譬襯
, b.currency_code.value('(牛覈)[1]', 'varchar(20)') 牛覈
, b.currency_code.value('(蟲螳覈)[1]', 'varchar(20)') 蟲螳覈
, convert(float, b.currency_code.value('(襷るГ蠍一)[1]', 'varchar(20)'))/100 襷るГ蠍一
from @xml.nodes(N'/PEXRATE/螻谿') as a(nth)
cross apply a.nth.nodes('牛貊') as b(currency_code)
where 1=1
and a.nth.value('@谿', 'varchar(8)') = '001' --1谿襷
[edit]
2 襷り覲 伎 #
--drop table #dim_account
create table #dim_account(
account_key int
, gender xml
, weekday_name xml
);
go
insert #dim_account values(1, N'<kr></kr><jp></jp>', N'<kr>覈</kr><jp></jp>');
insert #dim_account values(2, N'<kr></kr><jp>錵</jp>', N'<kr></kr><jp>羂</jp>');
go
--襷り覲襯 伎 select
declare @lang char(2)
set @lang = 'kr'
select
a.account_key
, b.gender.value('.', 'nchar(1)') gender
, c.weekday_name.value('.', 'nchar(1)') weekday_name
--, a.gender.value('kr[1]', 'nchar(1)') gender
from #dim_account a
cross apply (select a.gender.query('//*[local-name()=sql:variable("@lang")]')) as b(gender)
cross apply (select a.weekday_name.query('//*[local-name()=sql:variable("@lang")]')) as c(weekday_name)
[edit]
3 螳讌 xpath #
/*-----------------------------------------------------------------------------
Date : 1 May 2010
SQL Version: SQL Server 2005/2008
Author : Jacob Sebastian
Email : jacobvettickal@gmail.com
Twitter : @jacobvettickal
Blog : https://jacobsebastian.com
Website : https://jacobsebastian.com
Summary:
This script returns a tabular representation of an XML document
Modification History:
Jacob Sebastian - 1 May 2010
Created the first version
Jacob Sebatian - 18 June 2010
Fixed a bug in the XPath Expressiong generated
Jacob Sebastian - 20 June 2010
Added new column - ParentName
Updated the 'treeview' column to show lines
Added new column - 'Position'
Added New Column - 'ParentPosition'
Jacob Sebastian - 23 June 2010
Made the function UNICODE compatibile. (Thanks Peso)
Jacob Sebastian - 30 June 2010
Corrected the casing of a few columns to make the function
work on case sensitive SQL Server installations.
(Thanks Rhodri Evans)
Disclaimer:
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
-----------------------------------------------------------------------------*/
/*
SELECT * FROM dbo.XMLTable('
<employees>
<emp name="jacob"/>
<emp name="steve">
<phone>123</phone>
</emp>
</employees>
')
*/
CREATE FUNCTION [dbo].[XMLTable](
@x XML
)
RETURNS TABLE
AS RETURN
/*----------------------------------------------------------------------
This INLINE TVF uses a recursive CTE that processes each element and
attribute of the XML document passed in.
----------------------------------------------------------------------*/
WITH cte AS (
/*------------------------------------------------------------------
Anchor part of the recursive query. Retrieves the root element
of the XML document
------------------------------------------------------------------*/
SELECT
1 AS lvl,
x.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(NULL AS NVARCHAR(MAX)) AS ParentName,
CAST(1 AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
x.value('local-name(.)','NVARCHAR(MAX)') AS FullPath,
x.value('local-name(.)','NVARCHAR(MAX)')
+ N'['
+ CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS NVARCHAR)
+ N']' AS XPath,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS Position,
x.value('local-name(.)','NVARCHAR(MAX)') AS Tree,
x.value('text()[1]','NVARCHAR(MAX)') AS Value,
x.query('.') AS this,
x.query('*') AS t,
CAST(CAST(1 AS VARBINARY(4)) AS VARBINARY(MAX)) AS Sort,
CAST(1 AS INT) AS ID
FROM @x.nodes('/*') a(x)
UNION ALL
/*------------------------------------------------------------------
Start recursion. Retrieve each child element of the parent node
------------------------------------------------------------------*/
SELECT
p.lvl + 1 AS lvl,
c.value('local-name(.)','NVARCHAR(MAX)') AS Name,
CAST(p.Name AS NVARCHAR(MAX)) AS ParentName,
CAST(p.Position AS INT) AS ParentPosition,
CAST(N'Element' AS NVARCHAR(20)) AS NodeType,
CAST(
p.FullPath
+ N'/'
+ c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)
) AS FullPath,
CAST(
p.XPath
+ N'/'
+ c.value('local-name(.)','NVARCHAR(MAX)')
+ N'['
+ CAST(ROW_NUMBER() OVER(
PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS NVARCHAR )
+ N']' AS NVARCHAR(MAX)
) AS XPath,
ROW_NUMBER() OVER(
PARTITION BY c.value('local-name(.)','NVARCHAR(MAX)')
ORDER BY (SELECT 1)) AS Position,
CAST(
SPACE(2 * p.lvl - 1) + N'|' + REPLICATE(N'-', 1)
+ c.value('local-name(.)','NVARCHAR(MAX)') AS NVARCHAR(MAX)
) AS Tree,
CAST( c.value('text()[1]','NVARCHAR(MAX)') AS NVARCHAR(MAX) ) AS Value,
c.query('.') AS this,
c.query('*') AS t,
CAST(
p.Sort
+ CAST( (lvl + 1) * 1024
+ (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS VARBINARY(4)
) AS VARBINARY(MAX) ) AS Sort,
CAST(
(lvl + 1) * 1024
+ (ROW_NUMBER() OVER(ORDER BY (SELECT 1)) * 2) AS INT
)
FROM cte p
CROSS APPLY p.t.nodes('*') b(c)
), cte2 AS (
SELECT
lvl AS Depth,
Name AS NodeName,
ParentName,
ParentPosition,
NodeType,
FullPath,
XPath,
Position,
Tree AS TreeView,
Value,
this AS XMLData,
Sort, ID
FROM cte
UNION ALL
/*------------------------------------------------------------------
Attributes do not need recursive calls. So add the attributes
to the query output at the end.
------------------------------------------------------------------*/
SELECT
p.lvl,
x.value('local-name(.)','NVARCHAR(MAX)'),
p.Name,
p.Position,
CAST(N'Attribute' AS NVARCHAR(20)),
p.FullPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
p.XPath + N'/@' + x.value('local-name(.)','NVARCHAR(MAX)'),
1,
SPACE(2 * p.lvl - 1) + N'|' + REPLICATE('-', 1)
+ N'@' + x.value('local-name(.)','NVARCHAR(MAX)'),
x.value('.','NVARCHAR(MAX)'),
NULL,
p.Sort,
p.ID + 1
FROM cte p
CROSS APPLY this.nodes('/*/@*') a(x)
)
SELECT
ROW_NUMBER() OVER(ORDER BY Sort, ID) AS ID,
ParentName, ParentPosition,Depth, NodeName, Position,
NodeType, FullPath, XPath, TreeView, Value, XMLData
FROM cte2
DECLARE @cars xml;
SET @cars =
'<?xml version="1.0" encoding="UTF-8"?>
<Cars>
<Car id="1234">
<Make>Volkswagen</Make>
<Model>Eurovan</Model>
<Year>2003</Year>
<Color>White</Color>
</Car>
<Car id="5678">
<Make>Honda</Make>
<Model>CRV</Model>
<Year>2009</Year>
<Color>Black</Color>
<Mileage>35,600</Mileage>
</Car>
</Cars>';
SELECT xpath, value FROM dbo.xmlTable(@cars)
WHERE value is not null ORDER BY xpath
xpath Value
-------------------------------------
Cars[1]/Car[1]/@id 1234
Cars[1]/Car[1]/Color[1] White
Cars[1]/Car[1]/Make[1] Volkswagen
Cars[1]/Car[1]/Model[1] Eurovan
Cars[1]/Car[1]/Year[1] 2003
Cars[1]/Car[2]/@id 5678
Cars[1]/Car[2]/Color[1] Black
Cars[1]/Car[2]/Make[1] Honda
Cars[1]/Car[2]/Mileage[1] 35,600
Cars[1]/Car[2]/Model[1] CRV
Cars[1]/Car[2]/Year[1] 2009
[edit]
6 XQuery: Query(), Exist() #
declare @xml xml
set @xml =
'
<root>
<row>
<value>豌 覯讌 螳</value>
</row>
<row>
<value> 覯讌 螳</value>
</row>
</root>
'
select
@xml.query('data(/root/row[value = "豌 覯讌 螳"])')
, @xml.exist('/root/row/value[taxt() = " 覯讌 螳"]')
[edit]
7 Modify() #
create table #xml(x xml); insert #xml values( ' <root> <row> <value>豌 覯讌 螳</value> </row> <row> <value> 覯讌 螳</value> </row> </root> ' );
[edit]
7.1 replace value of #
update #xml
set x.modify('replace value of(/root/row[2]/value/text())[1] with " 覯讌 螳"');
[edit]
8 XQuery: Query(), Value() #
/*
DROP TABLE ETL_MetaData
CREATE TABLE ETL_MetaData(pkg_name nvarchar(500), dtsx xml)
go
INSERT ETL_MetaData
SELECT 'package2.dtsx', convert(xml, BulkColumn)
FROM OPENROWSET (BULK 'c:\package2.dtsx', SINGLE_BLOB) as t
*/
select
PackageName.value('
declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
(/DTS:Property)[1]', 'varchar(255)'
) PackageName
, Guid.value('
declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
(/DTS:Property)[1]', 'nvarchar(255)'
) Guid
from (
select
dtsx.query('
declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
/DTS:Executable[@DTS:ExecutableType="MSDTS.Package.1"]/DTS:Property[@DTS:Name="ObjectName"]') PackageName
, dtsx.query('
declare namespace DTS = "www.microsoft.com/SqlServer/Dts";
/DTS:Executable[@DTS:ExecutableType="MSDTS.Package.1"]/DTS:Property[@DTS:Name="DTSID"]') Guid
from etl_metadata
) t
/*
PackageName Guid
----------- ---------------------------------------
Package2 {FF3B203A-AE42-4890-B82C-39964D8F6481}
豢豢伎 {85963DBC-3FEC-4A62-9D4E-0882F15F5397}
*/
[edit]
9 XML #
declare
@list nvarchar(100);
set @list = 'a,c';
with x(xitems)
as
(select convert(xml, '<r>' + replace(@list, ',', '</r><r>') + '</r>') xitems)
select
y.items.value('.[1]', 'varchar(20)')
from x
cross apply x.xitems.nodes('/r') as y(items)
declare @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
[edit]
10 1 #
DECLARE @x varchar(5000)
SET @x = '
<Parameters>
<Row>
<ColA>34</ColA>
<ColB>10</ColB>
<ColC>4</ColC>
</Row>
<Row>
<ColA>36</ColA>
<ColB>11</ColB>
<ColC>1</ColC>
</Row>
</Parameters>';
SELECT
y.item.value('(ColA)[1]', 'varchar(50)') ColA
, y.item.value('(ColB)[1]', 'varchar(50)') ColB
, y.item.value('(ColC)[1]', 'varchar(50)') ColC
FROM (SELECT CONVERT(xml, @x) xitem) x
CROSS APPLY x.xitem.nodes('/Parameters/Row') as y(item)
[edit]
11 2 #
DECLARE @x varchar(5000)
SET @x = '
<Parameters>
<Row>
<GameSeq>34</GameSeq>
<TestType>10</TestType>
<TestDegree>4</TestDegree>
</Row>
<Row>
<GameSeq>51</GameSeq>
<TestType>10</TestType>
<TestDegree>5</TestDegree>
</Row>
<Row>
<GameSeq>36</GameSeq>
<TestType>11</TestType>
<TestDegree>1</TestDegree>
</Row>
</Parameters>';
SELECT
y.item.value('(GameSeq)[1]', 'varchar(50)') GameSeq
, y.item.value('(TestType)[1]', 'varchar(50)') TestType
, y.item.value('(TestDegree)[1]', 'varchar(50)') TestDegree
FROM (SELECT CONVERT(xml, @x) xitem) x
CROSS APPLY x.xitem.nodes('/Parameters/Row') as y(item)
[edit]
12 3 #
declare @x xml
set @x = (select * from dbo.譯殊讌 for xml path)
--set @x = (select * from dbo.譯殊讌 for xml auto)
select @x
/*
<row>
<讌覯>1</讌覯>
<覿襯>蟆伎</覿襯>
<讌>讌1</讌>
<る>讌1 る</る>
<一>1</一>
</row>
<row>
<讌覯>2</讌覯>
<覿襯>蟆伎</覿襯>
<讌>讌2</讌>
<る>讌2 る</る>
<一>3</一>
</row>
*/
select
y.item.value('(讌覯)[1]', 'int') 讌覯
, y.item.value('(覿襯)[1]', 'varchar(50)') 覿襯
, y.item.value('(讌)[1]', 'varchar(50)') 讌
, y.item.value('(る)[1]', 'varchar(500)') る
, y.item.value('(一)[1]', 'smallint') 一
from (select convert(xml, @x) xitem) x
cross apply x.xitem.nodes('/row') as y(item)
[edit]
13 谿瑚襭 #
![[http]](/moniwiki/imgs/http.png)
XQuery for the Non-Expert
- XML Workshop
- Using T-SQL to Transform XML Data to a Relational Format
- Gain Space Using XML data type
- XML Workshops
- A Generic Process to Convert XML Data - Part 2
- XML Support in SQL Server 2005
- FOR XML in SQL Server 2005
- XML Best Practices for SQL Server 2005
- Performance Optimizations for the XML Data Type in SQL Server 2005
- XML Options in SQL Server 2005
- A Generic Process to Convert XML Data - Part 1
- Split string using XML
- Using the FOR XML Clause to Return Query Results as XML
- Using the FOR XML Clause to Return Query Results as XML
- Upload new Attachment "Using_XML_In_SQL_Server_2005.pdf"
- http://www.sqlservercentral.com/Authors/Articles/Jacob_Sebastian/212008/
- Comparing Files from Different Folders Using SQL Server and XML
鏤