Archiv pro štítek: XML

Zřetězení sloupce řetězců do jednoho řetězce s oddělovačem 


CREATE TABLE #zretezit(slovo NVARCHAR(30))
INSERT INTO #zretezit(slovo)
	VALUES
	 (N'pondělí')
	,(N'úterý')
	,(N'středa')

DECLARE @zretezeni0 NVARCHAR(MAX) = (
	SELECT ','+z.slovo
	FROM #zretezit z
	FOR XML PATH('')
)
PRINT @zretezeni0
/*
Výsledek, všechno hraje jak má:
,pondělí,úterý,středa
*/

--Horší je, jak tam někdo nacpe html tagy a odřádkování
INSERT INTO #zretezit(slovo)
	VALUES
	(N'<b>text</b>')
	,(N'Lorem'+CHAR(13)+CHAR(10)+N'iposum')

DECLARE @zretezeni1 NVARCHAR(MAX) = (
	SELECT ','+z.slovo
	FROM #zretezit z
	FOR XML PATH('')
)
PRINT @zretezeni1
/*
Vysledkem je:
,pondělí,&lt;b&gt;text&lt;/b&gt;,Lorem&#x0D;
iposum
*/

--Pak pomáhá to zkonvertovat na NVARCHAR
DECLARE @zretezeni2 NVARCHAR(MAX) = (
	SELECT (
		SELECT ','+z.slovo
		FROM #zretezit z
		FOR XML PATH(''), TYPE
	).value('(./text())[1]','NVARCHAR(MAX)')
)
PRINT @zretezeni2
/*
Výsledek:
,pondělí,<b>text</b>,Lorem
iposum
*/

--Ještě se zbavit první čárky a máme tu konstrukci jak víno:
DECLARE @zretezeni3 NVARCHAR(MAX) = (
	SELECT STUFF(
	(
		SELECT ','+z.slovo
		FROM #zretezit z
		FOR XML PATH(''), TYPE
	).value('(./text())[1]','NVARCHAR(MAX)')
	,1
	,1
	,''
	)
)
PRINT @zretezeni3
/*
Krása střídá nádheru:
pondělí,úterý,středa,<b>text</b>,Lorem
iposum
*/

--Asi by bylo dobré zmínit, že od SQL 2017 funguje tohle
DECLARE @zretezeni4 NVARCHAR(MAX) = (
	SELECT STRING_AGG(z.slovo,',')
	FROM #zretezit z
)
PRINT @zretezeni4

Rychlokurz XML v 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