SQL Server, query multiple XML records in a table for a value, when multiple nodes/elements have the same name

Question

I have a table called xml_table with two columns: unique_id, xml_data. Each record in the table has different XML data in the xml_data column. Let's assume the XML schema looks something like below.

<ORDER ORDER_NAME="4800924">
  <ORDER_LINE LINE_NUM="1">
    <PROD NAME="A">
      <UOM_WEIGHT>0.31</UOM_WEIGHT>
    </PROD NAME>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="2">
    <PROD NAME="B">
      <UOM_WEIGHT>0.32</UOM_WEIGHT>
    </PROD NAME>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="3">
    <PROD NAME="C">
      <UOM_WEIGHT>0.33</UOM_WEIGHT>
    </PROD NAME>
  </ORDER_LINE>
</ORDER>

Now let's say I am searching for product B. I want the get a result set shown below from ALL the records in the table where any PROD/@NAME = 'B'...

   | ORDER_NAME | ORDER_LINE | PROD_NAME | UOM_WEIGHT |

If I were searching ONLY a single record of XML from the table I could do something like this...

select xml_data.value('(/ORDER/@ORDER_NAME)[1]', 'varchar(max)') as ORDER_NAME,
   xml_data.value('(/ORDER/ORDER_LINE/@LINE_NUM)[2]', 'varchar(max)') as ORDER_LINE,
   xml_data.value('(/ORDER/ORDER_LINE/PROD/@NAME)[2]', 'varchar(max)') as PROD_NAME,
   xml_data.value('(/ORDER/ORDER_LINE/PROD/UOM_WEIGHT)[2]', 'varchar(max)') as WEIGHT
from xml_table 
where  unique_id = 'blah'

by putting numbers in the brackets [1], [2] etc because I know exactly which element indexes have the product I am looking for.

However, what I need is this same result set for ALL records in the xml_table, while the product I'm looking for could be in ANY order_line element. Also, the total number of elements in any xml_Data is unknown.

Is there a way to effectively make the element level a wild card? I know this is not correct syntax, but something like...

select xml_data.value('(/ORDER/@ORDER_NAME)[1]', 'varchar(max)') as ORDER_NAME,
   xml_data.value('(/ORDER/ORDER_LINE/@LINE_NUM)[*]', 'varchar(max)') as ORDER_LINE,
   xml_data.value('(/ORDER/ORDER_LINE/PROD/@NAME)[*]', 'varchar(max)') as PROD_NAME,
   xml_data.value('(/ORDER/ORDER_LINE/PROD/UOM_WEIGHT)[*]', 'varchar(max)') as WEIGHT
from xml_table 
where  xml_data.value('(/ORDER/ORDER_LINE/PROD/@NAME)[*]', 'varchar(max)') = 'B'

... where instead of a hard coded integer for the element tree, it looks at all elements so that it returns results from ANY element that has the specific value I am looking for? That is effectively what I am trying to do.

Answer 1

The following code will show some techniques to get what you want:

DECLARE @xml_table TABLE(ID INT IDENTITY, Comment VARCHAR(100),xml_data XML);

INSERT INTO @xml_table VALUES
('Your example'
 ,N'<ORDER ORDER_NAME="4800924">
  <ORDER_LINE LINE_NUM="1">
    <PROD NAME="A">
      <UOM_WEIGHT>0.31</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="2">
    <PROD NAME="B">
      <UOM_WEIGHT>0.32</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="3">
    <PROD NAME="C">
      <UOM_WEIGHT>0.33</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
</ORDER>')
,('Example without a "B"'
,N'<ORDER ORDER_NAME="4800924">
  <ORDER_LINE LINE_NUM="1">
    <PROD NAME="A">
      <UOM_WEIGHT>0.31</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="2">
    <PROD NAME="Other">
      <UOM_WEIGHT>0.32</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="3">
    <PROD NAME="C">
      <UOM_WEIGHT>0.33</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
</ORDER>')
,('Example with two "Bs"'
,N'<ORDER ORDER_NAME="4800924">
  <ORDER_LINE LINE_NUM="1">
    <PROD NAME="A">
      <UOM_WEIGHT>0.31</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="2">
    <PROD NAME="B">
      <UOM_WEIGHT>0.32</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
  <ORDER_LINE LINE_NUM="3">
    <PROD NAME="B">
      <UOM_WEIGHT>0.33</UOM_WEIGHT>
    </PROD>
  </ORDER_LINE>
</ORDER>');

--The query will use .exist() to filter the rows to those with at least one NAME="B". If your table has got a lot of rows this will speed up your query. The alternative is to read the whole bunch of everything and use a WHERE at the final result set. This can lead to a huge overhead.

--The query will use .nodes() to get a derived table of all your <ORDER_LINE> nodes. Each node is returned alone. Using [1] on one of these is perfectly okay then

--The query uses sql:variable() within an XQuery predicate to filter against an externally declared variable. This allows you to use the same search with various filter values.

DECLARE @SearchFor VARCHAR(10)='B';

select ID
      ,Comment
      ,xml_data.value('(/ORDER/@ORDER_NAME)[1]', 'varchar(max)') as ORDER_NAME
      ,ol.value('@LINE_NUM', 'int') as ORDER_LINE
      ,ol.value('(PROD/@NAME)[1]', 'varchar(max)') as PROD_NAME
      ,ol.value('(PROD/UOM_WEIGHT)[1]', 'decimal(10,4)') as [WEIGHT]
from @xml_table AS xml_table 
cross apply xml_data.nodes('/ORDER/ORDER_LINE') AS A(ol)
where  xml_data.exist('/ORDER/ORDER_LINE/PROD[@NAME=sql:variable("@SearchFor")]') = 1;

The result (ID=2 does not show up)

+----+--------------------------+------------+------------+-----------+--------+
| ID | Comment                  | ORDER_NAME | ORDER_LINE | PROD_NAME | WEIGHT |
+----+--------------------------+------------+------------+-----------+--------+
| 1  | Your example             | 4800924    | 1          | A         | 0.3100 |
+----+--------------------------+------------+------------+-----------+--------+
| 1  | Your example             | 4800924    | 2          | B         | 0.3200 |
+----+--------------------------+------------+------------+-----------+--------+
| 1  | Your example             | 4800924    | 3          | C         | 0.3300 |
+----+--------------------------+------------+------------+-----------+--------+
| 3  | Example with    two "Bs" | 4800924    | 1          | A         | 0.3100 |
+----+--------------------------+------------+------------+-----------+--------+
| 3  | Example with    two "Bs" | 4800924    | 2          | B         | 0.3200 |
+----+--------------------------+------------+------------+-----------+--------+
| 3  | Example with    two "Bs" | 4800924    | 3          | B         | 0.3300 |
+----+--------------------------+------------+------------+-----------+--------+

hint
You can change the line with cross apply to this

cross apply xml_data.nodes('/ORDER/ORDER_LINE[PROD/@NAME=sql:variable("@SearchFor")]') AS A(ol)

...if you want to get only the "B" rows back. In this case you might go without the WHERE ....exist()

The result in this case

+----+--------------------------+------------+------------+-----------+--------+
| ID | Comment                  | ORDER_NAME | ORDER_LINE | PROD_NAME | WEIGHT |
+----+--------------------------+------------+------------+-----------+--------+
| 1  | Your example             | 4800924    | 2          | B         | 0.3200 |
+----+--------------------------+------------+------------+-----------+--------+
| 3  | Example with    two "Bs" | 4800924    | 2          | B         | 0.3200 |
+----+--------------------------+------------+------------+-----------+--------+
| 3  | Example with    two "Bs" | 4800924    | 3          | B         | 0.3300 |
+----+--------------------------+------------+------------+-----------+--------+

Answer 2

If I understand your question

Example

Select ORDER_NAME   = lvl1.n.value('@ORDER_NAME','int') 
      ,ORDER_LINE   = lvl2.n.value('@LINE_NUM','int') 
      ,PROD_NAME    = lvl3.n.value('@NAME','varchar(50)') 
      ,UOM_WEIGHT   = lvl3.n.value('UOM_WEIGHT[1]','decimal(10,2)') 
 From  @x.nodes('*') lvl1(n)
 Cross Apply lvl1.n.nodes('ORDER_LINE') lvl2(n)
 Cross Apply lvl2.n.nodes('PROD') lvl3(n)
 Where lvl3.n.value('@NAME','varchar(50)') ='B'

Returns

ORDER_NAME  ORDER_LINE  PROD_NAME   UOM_WEIGHT
4800924     2           B           0.32

Without the WHERE

ORDER_NAME  ORDER_LINE  PROD_NAME   UOM_WEIGHT
4800924     1           A           0.31
4800924     2           B           0.32
4800924     3           C           0.33