CODEWITHSUNDEEP
sqlsql-serverxml
Patriot
Patriot

Reputation: 312

Querying XML nodes in MS SQL

I have the following XML record.

<row>
  <c1>BUSINESS.HOME</c1>
  <c1 m="2">PAYMENTS.HOME</c1>
  <c1 m="3">DEPARTMENT.PAGE</c1>
  <c1 m="4">SECTION.HOME</c1>
  <c1 m="5">ABOUT.HOME</c1>

  <c2>Our Business</c2>
  <c2 m="1" s="2">Businesul nostru</c2>
  <c2 m="2">Payment Services</c2>
  <c2 m="2" s="2">Plati</c2>
  <c2 m="3">Department Operations</c2>
  <c2 m="3" s="2">Departamente</c2>
  <c2 m="4">Section Operations</c2>
  <c2 m="4" s="2">Sectiuni</c2>
  <c2 m="5">ABOUT</c2>
  <c2 m="5" s="2">Despre</c2>

  <c6>2</c6>
  <c10>GB0010001</c10>
  <c11>1</c11>
</row>

C2 are lables for C1 in two languages differentiated by the S attribute so the count of S2 is always double C1.

I would like to select a query with the following output.

NAME              LABEL
--------------------------------------
BUSINESS.HOME     Our Business
PAYMENTS.HOME     Payment Services
DEPARTMENT.PAGE   Department Operations
SECTION.HOME      Section Operations
ABOUT.HOME        ABOUT

Every value from C1 and the corresponding first value from C2.

I tried cross apply like this:

select t.p.query('.').value('data(./c2)[1]', 'varchar(max)') c2
from table_name 
cross apply XMLRECORD.nodes('(/row/c2)') t(p)
where ID  = 'HOME.PAGE'

But do not know how to proceed further in order to exclude the foreign language and include the C1 tag.

Upvotes: 1

Views: 99

Answers (2)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

You've got a working approach already. That's fine.

I just want to add an alternative, which should be better in performance.

DECLARE @XML xml = '
<row>
  <c1>BUSINESS.HOME</c1>
  <c1 m="2">PAYMENTS.HOME</c1>
  <c1 m="3">DEPARTMENT.PAGE</c1>
  <c1 m="4">SECTION.HOME</c1>
  <c1 m="5">ABOUT.HOME</c1>

  <c2>Our Business</c2>
  <c2 m="1" s="2">Businesul nostru</c2>
  <c2 m="2">Payment Services</c2>
  <c2 m="2" s="2">Plati</c2>
  <c2 m="3">Department Operations</c2>
  <c2 m="3" s="2">Departamente</c2>
  <c2 m="4">Section Operations</c2>
  <c2 m="4" s="2">Sectiuni</c2>
  <c2 m="5">ABOUT</c2>
  <c2 m="5" s="2">Despre</c2>

  <c6>2</c6>
  <c10>GB0010001</c10>
  <c11>1</c11>
</row>';

-- The query

WITH Tally(Nmbr) AS(SELECT TOP(@Xml.value('count(/row/c1)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values )
SELECT @Xml.value('(/row/c1/text())[sql:column("Nmbr")][1]','nvarchar(250)') AS [NAME]
      ,@xml.value('(/row/c2/text())[sql:column("Nmbr")*2-1][1]','nvarchar(250)') AS [LABEL]
FROM Tally;

The idea in short:

  • Create a tally-on-the-fly returning a list from 1 to n, where n is the count of <c1> elements (if you have a numbers table, use this instead)
  • Use the Tally's Nmbr column, and introduce it into the XQuery predicate with sql:column().
    • The first .value() calls the <c1> value by its position.
    • The second calls the corresponding value by the position x2 -1. without "-1" you would get the other language.

This is another approach, filtering for <c2> elements without the s attribute. In this case we do not need the computation with position x2 -1:

WITH Tally(Nmbr) AS(SELECT TOP(@Xml.value('count(/row/c1)','int')) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM master..spt_values )
SELECT @Xml.value('(/row/c1/text())[sql:column("Nmbr")][1]','nvarchar(250)') AS [NAME]
      ,@xml.value('(/row/c2[empty(@s)]/text())[sql:column("Nmbr")][1]','nvarchar(250)') AS [LABEL]
FROM Tally

Upvotes: 0

Thom A
Thom A

Reputation: 95554

This is a bit "ugly", due to having to match NULL on NULL, however, this is how I would achieve what you're after:

DECLARE @XML xml = '
<row>
  <c1>BUSINESS.HOME</c1>
  <c1 m="2">PAYMENTS.HOME</c1>
  <c1 m="3">DEPARTMENT.PAGE</c1>
  <c1 m="4">SECTION.HOME</c1>
  <c1 m="5">ABOUT.HOME</c1>

  <c2>Our Business</c2>
  <c2 m="1" s="2">Businesul nostru</c2>
  <c2 m="2">Payment Services</c2>
  <c2 m="2" s="2">Plati</c2>
  <c2 m="3">Department Operations</c2>
  <c2 m="3" s="2">Departamente</c2>
  <c2 m="4">Section Operations</c2>
  <c2 m="4" s="2">Sectiuni</c2>
  <c2 m="5">ABOUT</c2>
  <c2 m="5" s="2">Despre</c2>

  <c6>2</c6>
  <c10>GB0010001</c10>
  <c11>1</c11>
</row>';

SELECT c1.value('(./text())[1]','varchar(15)') AS [NAME],
       c2.value('(./text())[1]','varchar(15)') AS LABEL
FROM @XML.nodes('row/c1') c1(c1)
     JOIN @XML.nodes('row/c2') c2(c2) ON (c1.value('@m','int') = c2.value('@m','int') AND c2.value('@s','int') IS NULL)
                                      OR (c1.value('@m','int') IS NULL AND c2.value('@m','int') IS NULL);

Upvotes: 2

Related Questions