Leden | 2013 | Poznámky k T-SQL

Funkce nad xml

query() – dotaz nad XML, výsledkem je opět XML
value() – dotaz nad XML, vrátí hodnotu definovaného datového typu
exists() – test, zda dotaz vrátí neprázdnou hodnotu
modify() – modifikace XML
nodes() – dotaz nad XML, výsledkem je tabulka s jedním sloupcem typu XML

Do XQuery dotazu můžeme dostat SQL proměnnou díky funkci sql:variable(„@nazevPromenne“) nebo hodnotu z tabulky díky funkci sql:column(„tabulka.sloupec“) .

DECLARE @xml XML
SET @xml = '
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="vyvoj">
		<zamestnanec id="1">
			<jmeno>Jarmil</jmeno>
			<prijmeni>Novák</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">20000</plat>
		</zamestnanec>
		<zamestnanec id="2">
			<jmeno>Vlastimil</jmeno>
			<prijmeni>Hujer</prijmeni>
			<plat premie="ano" xmlns="dalsi-namespace">40000</plat>
		</zamestnanec>
	</oddeleni>
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat premie="ne" xmlns="dalsi-namespace">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
'

Vypořádání se s namespace

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace' 
)
SELECT @xml.value('(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace defaultního namespace

SELECT @xml.value('
declare default element namespace "nejaky-namespace";
(/zamestnanci/oddeleni[@typ="vyvoj"]/zamestnanec[@id="2"]/prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, jakýkoliv namespace

SELECT @xml.value('(/*:zamestnanci/*:oddeleni[@typ="vyvoj"]/*:zamestnanec[@id="2"]/*:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Přečtení jedné konkrétní hodnoty, deklarace namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/N:prijmeni)[1]','VARCHAR(50)') AS prijmeni
/*
prijmeni
--------------------------------------------------
Hujer
*/

Přečtení jedné konkrétní hodnoty, deklarace dvou namespace

SELECT @xml.value('
	declare namespace N="nejaky-namespace";
	declare namespace P="dalsi-namespace";
(/N:zamestnanci/N:oddeleni[@typ="vyvoj"]/N:zamestnanec[@id="2"]/P:plat)[1]','VARCHAR(50)') AS plat
/*
plat
--------------------------------------------------
40000
*/

Čtění z XML

Vypsání všech hodnot uzlu na konkrétní úrovni, absolutní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, --používám "dalsi-namespace"
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie --používám "dalsi-namespace"
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x) --dohledání uzlu, ke kterému se bude vztahovat funkce value
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

vypsání všech hodnot uzlu na konkrétní úrovni, relativní adresa

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat, 
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie 
FROM @xml.nodes('//zamestnanec') x(x)--relativní adresa
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Vlastimil                                          Hujer                                              40000       ano
Petr                                               Zeman                                              50000       ne
*/

podmínka na hodnotu atributu

;WITH XMLNAMESPACES(
    'dalsi-namespace' AS P,
    DEFAULT 'nejaky-namespace'
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec[@id="1"]') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
*/

podmínka na hodnotu atributu ve větší úrovni zanoření

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat[@premie="ne"]/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Jarmil                                             Novák                                              20000       ne
Petr                                               Zeman                                              50000       ne
*/

podmínka ve where

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]/@premie','VARCHAR(3)') = 'ano'
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Vlastimil                                          Hujer                                              40000       ano
*/

čtění na základě proměnné

DECLARE @promenna INT
SET @promenna = 50000

--čtění na základě proměnné, jednoduše ve where klauzuli
;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec') x(x)
WHERE x.value('P:plat[1]','INT') = @promenna
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

čtění na základě proměnné, sql proměnná protažená do XQuery výrazu

;WITH XMLNAMESPACES(
    DEFAULT 'nejaky-namespace',
    'dalsi-namespace' AS P 
)
SELECT
    x.value('jmeno[1]','VARCHAR(50)') AS jmeno,
    x.value('prijmeni[1]','VARCHAR(50)') AS prijmeni,
    x.value('P:plat[1]','INT') plat,
    x.value('P:plat[1]/@premie','VARCHAR(3)') premie
FROM @xml.nodes('/zamestnanci/oddeleni/zamestnanec/P:plat1/..') x(x)
/*
jmeno                                              prijmeni                                           plat        premie
-------------------------------------------------- -------------------------------------------------- ----------- ------
Petr                                               Zeman                                              50000       ne
*/

mazání

smazání uzlu

DECLARE @smaz XML
SET @smaz = @xml
SET @smaz.modify('delete /*:zamestnanci/*:oddeleni[@typ="vyvoj"]') --jakykoliv namespace
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
	<oddeleni typ="implementace">
		<zamestnanec id="3">
			<jmeno>Petr</jmeno>
			<prijmeni>Zeman</prijmeni>
			<plat xmlns="dalsi-namespace" premie="ne">50000</plat>
		</zamestnanec>
	</oddeleni>
</zamestnanci>
*/

smazání uzlu s deklarací dafaultniho namespace

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	delete /zamestnanci/oddeleni[@typ="vyvoj"]'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

smazání atributu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	delete /zamestnanci/oddeleni/zamestnanec/P:plat/@premie
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace">40000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

modifikace

bez cyklu lze modifikovat jen jednu hodnotu

SET @smaz = @xml
SET @smaz.modify('
	declare default element namespace "nejaky-namespace";
	declare namespace P = "dalsi-namespace";
	replace value of (/zamestnanci/oddeleni/zamestnanec[@id=2]/P:plat/text())[1]
	with "10000"
	'
)
SELECT @smaz
/*
<zamestnanci xmlns="nejaky-namespace">
  <oddeleni typ="vyvoj">
    <zamestnanec id="1">
      <jmeno>Jarmil</jmeno>
      <prijmeni>Novák</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">20000</plat>
    </zamestnanec>
    <zamestnanec id="2">
      <jmeno>Vlastimil</jmeno>
      <prijmeni>Hujer</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ano">10000</plat>
    </zamestnanec>
  </oddeleni>
  <oddeleni typ="implementace">
    <zamestnanec id="3">
      <jmeno>Petr</jmeno>
      <prijmeni>Zeman</prijmeni>
      <plat xmlns="dalsi-namespace" premie="ne">50000</plat>
    </zamestnanec>
  </oddeleni>
</zamestnanci>
*/

Další zdroje
http://beyondrelational.com/modules/2/blogs/28/posts/10279/xquery-labs-a-collection-of-xquery-sample-scripts.aspx
http://www.w3schools.com/xpath/default.asp
http://www.w3schools.com/xquery/default.asp

Poznámky k T-SQL

píše Jakub Rychlý

Archiv pro měsíc: Leden 2013

Rychlokurz XML v T-SQL

Funkce nad xml

Tabulka použitá v reportech