SQL Server retrieve all values

Question

I have a table, let's call it TBL, with a column of type XML.

The XML column (called xml here) is of the format:

<START xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="87jjhanM">
    <Header xmlns="">
    ...
    </Header>
    <Fetch xmlns="">
    .....
    </Fetch>
    <Send xmlns="">
        <Supplier>
            <Deals>
                <Deal>
                   <Field1> </Field1>
                   <Field2> </Field2>
                </Deal>
            </Deals>
        </Supplier>
        <Supplier>
            <Deals>
                <Deal>
                   <Field1> </Field1>
                   <Field2> </Field2>
                </Deal>
            </Deals>
        </Supplier>
    </Send>
</START>

Notice that each XML document can have multiple <Supplier> tags. What I am interested in, is getting the values of Field1 and Field2.

From reading e.g How can I query a SQL Server XML column and return all values for a specific node? and Getting multiple records from xml column with value() in SQL Server it seems that I should be using some sort of cross apply.

I just can't seem to make it work. With value(), I am perfectly capable of getting the first instance found, but with nodes() I fail miserably (SQL Server).

My blatant rip-off attempt (here only for Field1) for getting all the Field1 values are:

SELECT  
    xml.value('(/Supplier/Deals/Deal/Field1[1])[1]', 'VARCHAR(100)') AS A
FROM    
    TBL
CROSS APPLY 
    xml.nodes('/Start/Send') x(A);

which, as far as I understand, simply looks for the first value /Supplier/Deals/Deal/Field1 under each /Start/Send/, but it does not work - it simply returns 0 rows affected (I know the specific structure exist in the XML files I should say).

I am sure this is quite easy, but I just can't wrap my head around why the above does not work. Any help is greatly appreciated.

Shnugo · Answer 1 · 2018-02-12T15:36:23.807

You tried to shorten and clean this for brevety, that's good. But - at least I think so - you did a bit to much...

Jeroen Mostert has pointed already to the casing ("START"!="Start") and the namespaces. There is a (rather odd) defualt namespace in the first line, and some namespaces you do not use at all.

The repeating xmlns="" within your XML are quite dangerous. This is probably created by a T-SQL command with FOR XML and sub-selects. The point is: This is not just something silly to ignore, but you are defining a new default namespace for the inner elements. That's why I used the namespace wildcard *: and omitted the namespace declaration.

I added some additional elements to each level and assumed, that there can be 1:n <Supplier> and 1:n <Deal> nodes below <Deals> (at least the naming points to this). You can use two levels of OUTER / CROSS APPLY for this:

DECLARE @mockupTBL TABLE(ID INT IDENTITY, TheXml XML);
INSERT INTO @mockupTBL VALUES
(N'<START xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="87jjhanM">
   <Header xmlns="">
      <SomeNodeWithinHeader SomeValue="blah"/>
   </Header>
 <Fetch xmlns="">
      <SomeNodeWithinFetch SomeValue="blubb"/>
 </Fetch>
 <Send xmlns="">
   <Supplier>
     <SupplierRelatedData value="Sup1"/>
     <Deals>
       <Deal>
         <Field1>A1</Field1>
         <Field2>A2</Field2>
       </Deal>
       <Deal>
         <Field1>A3</Field1>
         <Field2>A4</Field2>
       </Deal>
     </Deals>
   </Supplier>
   <Supplier>
     <SupplierRelatedData value="Sup2"/>
     <Deals>
       <Deal>
         <Field1>B1</Field1>
         <Field2>B2</Field2>
       </Deal>
     </Deals>
   </Supplier>
 </Send>
 </START>');

 SELECT m.TheXml.value(N'(/*:START/Header/SomeNodeWithinHeader/@SomeValue)[1]',N'nvarchar(max)') ValueWithinHeader
       ,m.TheXml.value(N'(/*:START/Fetch/SomeNodeWithinFetch/@SomeValue)[1]',N'nvarchar(max)') ValueWithinFetch
       ,sup.value(N'(SupplierRelatedData/@value)[1]',N'nvarchar(max)') SupplierRelatedData
       ,deal.value(N'(Field1/text())[1]',N'nvarchar(max)') AS Field1
       ,deal.value(N'(Field2/text())[1]',N'nvarchar(max)') AS Field2
 FROM @mockupTBL AS m
 OUTER APPLY m.TheXml.nodes(N'/*:START/Send/Supplier') AS A(sup)
 OUTER APPLY A.sup.nodes(N'Deals/Deal') AS B(deal)

The result

blah    blubb   Sup1    A1  A2
blah    blubb   Sup1    A3  A4
blah    blubb   Sup2    B1  B2

score 1 · Accepted Answer · answered Feb 12 '18 at 15:24

Quite a few things wrong that are probably not directly relevant to your question: XML is case-sensitive (so START, not Start), the outer element is in a namespace (87jjhanM) and so requires a WITH XMLNAMESPACES, that sort of thing. But assuming we sort all that out:

WITH XMLNAMESPACES ('87jjhanM' AS n)
SELECT 
    A.value('Field1[1]', 'VARCHAR(100)') AS Field1, 
    A.value('Field2[1]', 'VARCHAR(100)') AS Field2
FROM    TBL
CROSS APPLY [xml].nodes('/n:START/Send/Supplier/Deals/Deal') x(A);

The CROSS APPLY should ideally be done on the lowest repeating element we're interested in (Deal in this case) to make the value queries as simple as possible, but this is not a hard requirement.

If instead you wanted all values in Field1 and Field2, but you don't care what field they're actually in, you could match by name:

WITH XMLNAMESPACES ('87jjhanM' AS n)
SELECT 
    A.value('.', 'VARCHAR(100)') AS [Field]
FROM    TBL
CROSS APPLY [xml].nodes('/n:START/Send/Supplier/Deals/Deal/*[local-name()="Field1" or local-name()="Field2"]') x(A);

If you wanted to, say, get everything in every element starting with Field... well then things get a lot more annoying, but fortunately you said you know the structure of the XML involved, so that should not be an issue.

SQL Server retrieve all values

2 Answers2