I looked at some threads but I think I'm missing something in Microsoft SQL Server (SSMS).

I have XML in column defined as XML datatype that looks like this:

(I erased stuff before this not sure if it's needed)

<ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
    <ItemData ItemOID="TEACHER" Value="145"/>
    <ItemData ItemOID="AGE" Value="50" />
</ItemGroupData>
<ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
    <ItemData ItemOID="TEACHER" Value="151"/>
    <ItemData ItemOID="AGE" Value="42" /> 
</ItemGroupData>

There's stuff I truncated but what is the most optimal way to locate the XML file where teacher 145 is and they can be in any of the Itemdata groups?

I can find it like:

SELECT 
    CAST(XML AS nvarchar(max)) AS test 
FROM
    table1 
WHERE
    XML LIKE '%' 

but I am looking into learning different ways without casting unless that is the most optimal way?

CodePudding user response：

SQL Server supports powerful XQuery language to deal with the XML data type.

Please try the following solution.

It is using XPath predicate [@Value=sql:variable("@TeacherValue")] to search directly in the XML data type.

The sql:variable("@TeacherValue") construct allows to pass a parameter to it.

Also, SQL Server supports XML indexes for that.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, XmlData XML);
INSERT INTO @tbl (XmlData) VALUES
(N'<root>
    <ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
        <ItemData ItemOID="TEACHER" Value="145"/>
        <ItemData ItemOID="AGE" Value="50"/>
    </ItemGroupData>
    <ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
        <ItemData ItemOID="TEACHER" Value="151"/>
        <ItemData ItemOID="AGE" Value="42"/>
    </ItemGroupData>
</root>');
-- DDL and sample data population, end

DECLARE @TeacherValue INT = 145;

SELECT t.ID
    , p.value('@ItemGroupOID', 'VARCHAR(50)') AS ItemGroupOID
    , p.value('@TransactionType', 'VARCHAR(50)') AS TransactionType
    , c.value('@ItemOID', 'VARCHAR(25)') AS ItemOID
    , c.value('@Value', 'INT') AS value
FROM @tbl AS t
CROSS APPLY XmlData.nodes('/root/ItemGroupData[ItemData[@ItemOID="TEACHER"
    and @Value=sql:variable("@TeacherValue")]]') AS t1(p)
CROSS APPLY t1.p.nodes('ItemData') AS t2(c);

Output

CodePudding user response：

Yes, that "stuff before" that you erased will be very important! You need to build up XPath expressions to select individual pieces from the XML - and those depend on everything from the root on down! Also - you might have XML namespaces that are defined in the "stuff before" - which you need to respect to get any results.

Anyhoo - ASSUMING you have just a <root>....</root> node before your XML, you could get your desired result like this :

DECLARE @XmlTbl TABLE (ID INT NOT NULL, XmlData XML)

INSERT INTO @XmlTbl (ID, XmlData)
VALUES (1, 
    '<root><ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
    <ItemData ItemOID="TEACHER" Value="145"/>
    <ItemData ItemOID="AGE" Value="50" />
</ItemGroupData>
<ItemGroupData ItemGroupOID="TEST" TransactionType="Insert">
    <ItemData ItemOID="TEACHER" Value="151"/>
    <ItemData ItemOID="AGE" Value="42" /> 
</ItemGroupData></root>')

SELECT
    t.ID,
    XC.value('(@ItemGroupOID)', 'varchaR(50)') AS ItemGroupOID,
    XC.value('(@TransactionType)', 'varchaR(50)') AS TransactionType,
    XC2.value('@ItemOID', 'varchar(25)') AS ItemOID,
    XC2.value('@Value', 'int') AS value
FROM
    @XmlTbl t
CROSS APPLY    -- "enumerate" the <ItemGroupData> nodes under <root>
    XmlData.nodes('/root/ItemGroupData') AS XT(XC)
CROSS APPLY    -- "enumerate" the <ItemData> subnodes
    XC.nodes('ItemData') AS XT2(XC2)
WHERE
    XC.value('(ItemData/@Value)[1]', 'int') = 145

This would return these results: