_ | 覦覈襦 | 豕蠏手 | 殊螳 | 譯殊碁
FrontPage › XML

Contents

1
2 襷り覲 伎
3 螳讌 xpath
4 Well Formed XML?
5 語 襾豪?
6 XQuery: Query(), Exist()
7 Modify()
7.1 replace value of
7.2 insert
7.3 delete
8 XQuery: Query(), Value()
9 XML
10 1
11 2
12 3
13 谿瑚襭


覓語 譴..

蟯碁ゼ 覺.
 @message_body.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

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谿襷

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)


3 螳讌 xpath #

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


4 Well Formed XML? #

5 語 襾豪? #


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() = " 覯讌 螳"]')

7 Modify() #

create table #xml(x xml);

insert #xml values(
'
<root>
	<row>
		<value>豌 覯讌 螳</value>
	</row>
	<row>
		<value> 覯讌 螳</value>
	</row>
</root>
'
);

7.1 replace value of #
update #xml
set x.modify('replace value of(/root/row[2]/value/text())[1] with " 覯讌 螳"');

7.2 insert #

7.3 delete #

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}
*/

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)

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)

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)

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)
蠍 蠍郁鍵..
企: : るジ讓曙 襦螻豺 企Ν 譯殊語. 襦螻豺
EditText : Print : Mobile : FindPage : DeletePage : LikePages : Powered by MoniWiki : Last modified 2019-06-24 17:10:42

瑚 覈旧朱 る 蠏碁 觜讌 蟆企. 讌襷 蠏碁ゼ 覩碁 螳ロ 覈旧朱 覦朱慨 蠏碁覃 蠏碁 襷襦 蠏碁 蟆企. (蟯危)