TSQL

I have an XML in this format

<tests>
    <test>
        <testid>1</testid>
        <testval>8</testval>
        <testname>
            <testid>1</testid>
            <testname>test 1</testname>
        </testname>
    </test>
    <test>
        <testid>2</testid>
        <testval>5</testval>
        <testname>
            <testid>2</testid>
            <testname>test 2</testname>
        </testname>
    </test>
</tests>

using TSQL/XML query how do I achieve this result

[Testid][TestVal][TestName]
    1       8      Test 1
    2       5      Test 2

How do I query XML data in SQL?

SQL Server provides the XQuery feature to querying XML data type or querying with the XML column with the XPATH. Using XQuery, users can Insert, Update and Delete with the XML nodes and node values in an XML column.

How can I get SQL query results in XML?

You can optionally retrieve formal results of a SQL query as XML by specifying the FOR XML clause in the query. The FOR XML clause can be used in top-level queries and in subqueries. The top-level FOR XML clause can be used only in the SELECT statement.

How do I query values in XML nodes?

You can retrieve multiple values from the rowset. For example, you can apply the value() method to the rowset returned by nodes() and retrieve multiple values from the original XML instance. The value() method, when applied to the XML instance, returns only one value.

How do I select a specific XML node in SQL Server?

You should use the query() Method if you want to get a part of your XML. If you want the value from a specific node you should use value() Method. Update: If you want to shred your XML to multiple rows you use nodes() Method.

Try this:

declare @input XML = '<tests>
    <test>
        <testid>1</testid>
        <testval>8</testval>
        <testname>
            <testid>1</testid>
            <testname>test 1</testname>
        </testname>
    </test>
    <test>
        <testid>2</testid>
        <testval>5</testval>
        <testname>
            <testid>2</testid>
            <testname>test 2</testname>
        </testname>
    </test>
</tests>'

select
    Tests.value('(testid)[1]', 'int') as 'TestID',
    Tests.value('(testval)[1]', 'int') as 'TestVal',
    Tests.value('(testname/testname)[1]', 'varchar(20)') as 'TestName'
FROM 
    @input.nodes('/tests/test') as List(Tests)

This gives you the desired output.

If you have a table of those XML columns, you might need to use a slightly different approach (using CROSS APPLY):

select
    tbl.SomeValue, tbl.SomeOtherValue,
    Tests.value('(testid)[1]', 'int') as 'TestID',
    Tests.value('(testval)[1]', 'int') as 'TestVal',
    Tests.value('(testname/testname)[1]', 'varchar(20)') as 'TestName'
FROM 
    dbo.YourTable tbl
CROSS APPLY
    tbl.XmlColumn.nodes('/tests/test') as List(Tests)

TSQL - XML query help

Tags:

sql-server

xml

xpath

Juvil

People also ask

1 Answers

marc_s

Recent Activity

Donate For Us