CODEWITHSUNDEEP
sql-serverxmlt-sqlxpathxquery
Arkadiusz
Arkadiusz

Reputation: 427

How to get all elements using an absolute nodes method?

I have a table with xml data. I'd like to get all the information using nodes method. What I'd like to accomplish is to get all elements, I mean if there are two addresses I want to get them.

Here's an example data:

            CREATE TABLE #xml (xml_data XML)

            INSERT INTO #xml
            VALUES (
                '<WynikWyszukiwania>
                <InformacjaOWpisie>
                    <IdentyfikatorWpisu>bb2c8c14dd42fee6d75f9fad89b4fae8</IdentyfikatorWpisu>
                    <DanePodstawowe>
                        <Imie>JOHN</Imie>
                        <Nazwisko>SMITH</Nazwisko>
                        <NIP>0000000000</NIP>
                        <REGON>0000000000000</REGON>
                        <Firma>XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX"</Firma>
                    </DanePodstawowe>
                    <DaneKontaktowe>
                        <AdresPocztyElektronicznej />
                        <AdresStronyInternetowej />
                        <Telefon />
                        <Faks />
                    </DaneKontaktowe>
                    <DaneAdresowe>
                        <AdresGlownegoMiejscaWykonywaniaDzialalnosci>
                            <TERC>1261011</TERC>
                            <SIMC>0950463</SIMC>
                            <Miejscowosc>Kraków</Miejscowosc>
                            <Ulica>XXX</Ulica>
                            <Budynek>1</Budynek>
                            <KodPocztowy>01-0001</KodPocztowy>
                            <Poczta>Kraków</Poczta>
                            <Gmina>Kraków</Gmina>
                            <Powiat>Kraków</Powiat>
                            <Wojewodztwo>MAŁOPOLSKIE</Wojewodztwo>
                        </AdresGlownegoMiejscaWykonywaniaDzialalnosci>
                        <AdresyDodatkowychMiejscWykonywaniaDzialalnosci>
                            <Adres>
                                <Miejscowosc>KRAKÓW</Miejscowosc>
                                <Ulica>YYY</Ulica>
                                <Budynek>1</Budynek>
                                <KodPocztowy />
                                <Poczta>KRAKÓW</Poczta>
                                <Kraj>Polska</Kraj>
                            </Adres>
                            <Adres>
                                <TERC>1021022</TERC>
                                <SIMC>0725849</SIMC>
                                <Miejscowosc>Kraków</Miejscowosc>
                                <Budynek>1</Budynek>
                                <KodPocztowy />
                                <Poczta>Kraków</Poczta>
                                <Kraj>Polska</Kraj>
                                <Gmina>BRZEZINY</Gmina>
                                <Powiat>BRZEZIŃSKI</Powiat>
                                <Wojewodztwo>ŁÓDZKIE</Wojewodztwo>
                            </Adres>
                        </AdresyDodatkowychMiejscWykonywaniaDzialalnosci>
                        <AdresDoDoreczen>
                            <TERC>1261011</TERC>
                            <SIMC>0950463</SIMC>
                            <Miejscowosc>KRAKÓW</Miejscowosc>
                            <Ulica>WWW</Ulica>
                            <Budynek>175</Budynek>
                            <KodPocztowy>30-698</KodPocztowy>
                            <Poczta>KRAKÓW</Poczta>
                            <Gmina>KRAKÓW</Gmina>
                            <Powiat>KRAKÓW</Powiat>
                            <Wojewodztwo>MAŁOPOLSKIE</Wojewodztwo>
                        </AdresDoDoreczen>
                        <PrzedsiebiorcaPosiadaObywatelstwaPanstw>Polska</PrzedsiebiorcaPosiadaObywatelstwaPanstw>
                    </DaneAdresowe>
                    <DaneDodatkowe>
                        <DataRozpoczeciaWykonywaniaDzialalnosciGospodarczej>1983-10-29</DataRozpoczeciaWykonywaniaDzialalnosciGospodarczej>
                        <DataZawieszeniaWykonywaniaDzialalnosciGospodarczej />
                        <DataWznowieniaWykonywaniaDzialalnosciGospodarczej />
                        <DataZaprzestaniaWykonywaniaDzialalnosciGospodarczej />
                        <DataWykresleniaWpisuZRejestru />
                        <MalzenskaWspolnoscMajatkowa>-</MalzenskaWspolnoscMajatkowa>
                        <Status>Aktywny</Status>
                    </DaneDodatkowe>
                    <SpolkiCywilneKtorychWspolnikiemJestPrzedsiebiorca>
                        <InformacjeOSpolce>
                            <NIP>0000000000000000</NIP>
                            <REGON />
                        </InformacjeOSpolce>
                    </SpolkiCywilneKtorychWspolnikiemJestPrzedsiebiorca>
                    <Zakazy />
                    <InformacjeDotyczaceUpadlosciPostepowaniaNaprawczego />
                    <DataZgonu />
                    <Sukcesja />
                </InformacjaOWpisie>
            </WynikWyszukiwania>'
                )

There are two values for AdresyDodatkowychMiejscWykonywaniaDzialalnosci. It doesn't pose a problem to get these addresses if I use a relative method.

            SELECT isnull(a.x.value('(KodPocztowy)[1]', 'varchar(255)'), '') + ' ' + isnull(a.x.value('(Miejscowosc)[1]', 'varchar(255)'), '') + ' ' + isnull(a.x.value('(Ulica)[1]', 'varchar(255)'), '') + ' ' + ISNULL(a.x.value('(Budynek)[1]', 'varchar(255)'), '') + CASE 
                    WHEN ISNULL(a.x.value('(Lokal)[1]', 'varchar(255)'), '') <> ''
                        THEN + '/' + a.x.value('(Lokal)[1]', 'varchar(255)')
                    ELSE ''
                    END AS AdresyDodatkowychMiejscWykonywaniaDzialalnosci
            FROM #xml
            CROSS APPLY xml_data.nodes('/WynikWyszukiwania/InformacjaOWpisie/DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres') AS a(x)

The biggest problem is that I need to get all data except for this one column. So when I use this query in absolute way I get only one address.

        SELECT isnull(a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/KodPocztowy)[1]', 'varchar(255)'), '') + ' ' + isnull(a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/Miejscowosc)[1]', 'varchar(255)'), '') + ' ' + isnull(a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/Ulica)[1]', 'varchar(255)'), '') + ' ' + ISNULL(a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/Budynek)[1]', 'varchar(255)'), '') + CASE 
                    WHEN ISNULL(a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/Lokal)[1]', 'varchar(255)'), '') <> ''
                        THEN + '/' + a.x.value('(DaneAdresowe/AdresyDodatkowychMiejscWykonywaniaDzialalnosci/Adres/Lokal)[1]', 'varchar(255)')
                    ELSE ''
                    END AS AdresyDodatkowychMiejscWykonywaniaDzialalnosci
            FROM #xml
            CROSS APPLY xml_data.nodes('/WynikWyszukiwania/InformacjaOWpisie') AS a(x)

My desired output are two rows:

KRAKÓW YYY 1,
Kraków 1

for AdresyDodatkowychMiejscWykonywaniaDzialalnosci and all other information.

Upvotes: 0

Views: 75

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

Your XML is not so easy to query. If this is under your control and you can change it, it would be better to use the same element name toegether with an attribute, instead of named elements of the same kind.

e.g.

<AdresList type="GlownegoMiejscaWykonywaniaDzialalnosci" />
<AdresList type="DodatkowychMiejscWykonywaniaDzialalnosci" />

And it would be better to use the same structure in any case. If you might have repeated elements, you should use <Adres> in any case, not just in cases, where there is more than one address.

The following query will give you an example for any place in your XML. I hope, this will be enough to find the correct Xpath for any value you need.

SELECT tXml.xml_data.value('(/WynikWyszukiwania/InformacjaOWpisie/IdentyfikatorWpisu/text())[1]','nvarchar(max)') AS IdentyfikatorWpisu

      ,tXml.xml_data.value('(/WynikWyszukiwania/InformacjaOWpisie/DanePodstawowe/Imie/text())[1]','nvarchar(max)') AS DanePodstawowe_Imie
      --more of <DanePodstawowe>

      ,tXml.xml_data.value('(/WynikWyszukiwania/InformacjaOWpisie/DaneKontaktowe/Imie/text())[1]','nvarchar(max)') AS DaneKontaktowe_AdresPocztyElektronicznej
      --more of <DaneKontaktowe>

      ,A.AllAddressNodes.value('local-name(.)','nvarchar(max)') AddressNode_Name
      ,COALESCE(B.repeatedAddress.value('(TERC/text())[1]','nvarchar(max)'), A.AllAddressNodes.value('(TERC/text())[1]','nvarchar(max)')) AS Address_TERC
      ,COALESCE(B.repeatedAddress.value('(SIMC/text())[1]','nvarchar(max)'), A.AllAddressNodes.value('(SIMC/text())[1]','nvarchar(max)')) AS Address_SIMC
      ,COALESCE(B.repeatedAddress.value('(Miejscowosc/text())[1]','nvarchar(max)'), A.AllAddressNodes.value('(Miejscowosc/text())[1]','nvarchar(max)')) AS Address_Miejscowosc
      ,COALESCE(B.repeatedAddress.value('(Budynek/text())[1]','nvarchar(max)'), A.AllAddressNodes.value('(Budynek/text())[1]','nvarchar(max)')) AS Address_Budynek

      ,tXml.xml_data.value('(/WynikWyszukiwania/InformacjaOWpisie/DaneAdresowe/PrzedsiebiorcaPosiadaObywatelstwaPanstw/text())[1]','nvarchar(max)') AS Address_PrzedsiebiorcaPosiadaObywatelstwaPanstw
FROM #xml tXml
OUTER APPLY tXml.xml_data.nodes('/WynikWyszukiwania/InformacjaOWpisie/DaneAdresowe/*[contains(local-name(),"Adres")]') A(AllAddressNodes)
OUTER APPLY A.AllAddressNodes.nodes('Adres') B(repeatedAddress);

The idea in short:

We fetch values, which live in your XML just once, directly using .value() and the full path. This is fast, than using .nodes().

For the multiple Address nodes we use .nodes() with the *, but we place a predicate to get only nodes where the word Adres is part of the element's name.

We use another .nodes() against the fragment using a relative path.

The SELECT uses .value() with local-name() in order to find the specific address kind.

Then it uses COALESCE as we do not know, if the value is taken from the single address element or from the repeated address list. As far as I see you do not mix them.

Upvotes: 2

Related Questions