Archiv pro měsíc: Leden 2013

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

Tabulka použitá v reportech

Často se stane, že změníme strukturu databázové tabulky a nevíme, které reporty tím rozbijeme. Následujícím dotazem nad databází report serveru můžeme dohledat reporty, které v příkazu obsahují hledaný text, třeba název tabulky, kterou jsme změnili.

DECLARE @text varchar(250)
SET @text = 'hledany text'
 
;WITH xmlCatalog AS
(
    SELECT
         Name
        ,Path
        ,CONVERT(xml,CONVERT(varbinary(max),content)) AS Content
    FROM ReportServer.dbo.Catalog
    WHERE Type  = 2
)
SELECT
    name,
    path,
    Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM xmlCatalog
CROSS APPLY xmlCatalog.Content.nodes('//*:Query') Queries(Query)
WHERE Query.value('(./*:CommandText/text())[1]','nvarchar(max)') like '%'+@text+'%'