Querying XML in SQL Server

Question

I'm a newbie to SQL Server. I have a table Accounts which is defined as:

OrganizationId int,
AccountDetails varchar(max)

The AccountDetails column contains XML data.

The data in the table looks like this:

1 | <Account><Id>100</Id><Name>A</Name></Account>
2 | <Account><Id>200</Id><Name>B</Name></Account>
3 | <Account><Id>300</Id><Name>C</Name></Account>
4 | <Account><Id>400</Id><Name>D</Name></Account>

I need write a SQL query to get the records from this table where AccountId is 200 or 400.

The query should return two rows (#2 and #4) in JSON format, like this:

result1 : { "account_id": 200, "account_name": B }
result2 : { "account_id": 400, "account_name": D }

I'm wondering how do I go about this?

Thank you.

For # 1 above, should I be trying to cast the AccountDetails column to XML type and then use "nodes" feature for querying/filtering?

For #2, I should be writing a SQL function to convert the XML to JSON first and querying XML to build the JSON as needed?

Aside... that's a horrible schema for storing XML data. Not only is it inefficient to convert text to XML whenever you need to query it (XML is stored in a special format by SQL server) but by using a char/varchar data type like that you'll likely clobber international characters. Save yourself some time and trouble, use the correct xml data type. — AlwaysLearning
– AlwaysLearning, Commented Nov 10, 2022 at 2:39
#1: yes. #2: no, deserialize from XML to native values first, filter your result set, then use FOR JSON to serialize the filtered results to JSON. — AlwaysLearning
– AlwaysLearning, Commented Nov 10, 2022 at 2:40
Building on the comments from @AlwaysLearning store your values in their own, relational columns and not in XML at all! That won't help with your current predicament, but there are XML functions available in sql server. — Patrick Hurst
– Patrick Hurst, Commented Nov 10, 2022 at 3:39
While asking a question, you need to provide a minimal reproducible example: (1) DDL and sample data population, i.e. CREATE table(s) plus INSERT T-SQL statements. (2) What you need to do, i.e. logic and your code attempt implementation of it in T-SQL. (3) Desired output, based on the sample data in the #1 above. (4) Your SQL Server version (SELECT @@version;). — Yitzhak Khabinsky
– Yitzhak Khabinsky, Commented Nov 10, 2022 at 4:29
Thank you Yitzhak for your valuable feedback! Noted, going forward. — Mitu
– Mitu, Commented Nov 10, 2022 at 15:44

Yitzhak Khabinsky · Accepted Answer · 2022-11-10 04:28:59Z

1

As already mentioned, it is much better to use a proper XML data type for the AccountDetails column.

Please try the following solution.

It will work starting from SQL Server 2016 onwards.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (OrganizationId INT IDENTITY PRIMARY KEY, AccountDetails NVARCHAR(MAX));
INSERT @tbl (AccountDetails) VALUES
('<Account><Id>100</Id><Name>A</Name></Account>'),
('<Account><Id>200</Id><Name>B</Name></Account>'),
('<Account><Id>300</Id><Name>C</Name></Account>'),
('<Account><Id>400</Id><Name>D</Name></Account>');
-- DDL and sample data population, end

;WITH rs AS
(
    SELECT t.OrganizationId
        , account_id = x.value('(/Account/Id/text())[1]', 'INT')
        , account_name = x.value('(/Account/Name/text())[1]', 'VARCHAR(20)')
    FROM @tbl AS t
        CROSS APPLY (VALUES(TRY_CAST(AccountDetails AS XML))) AS t1(x)
)
SELECT *
    , JSONData = (SELECT rs.account_id, rs.account_name FOR JSON PATH,WITHOUT_ARRAY_WRAPPER)
FROM rs
WHERE rs.account_id IN (200, 400);

Output

OrganizationId	account_id	account_name	JSONData
2	200	B	{"account_id":200,"account_name":"B"}
4	400	D	{"account_id":400,"account_name":"D"}

answered Nov 10, 2022 at 4:28

Yitzhak Khabinsky

22.7k2 gold badges19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mitu Over a year ago

Thank you for your inputs on this. For #1, I was trying below earlier, but keep getting "unable to switch encoding" error. I shall try your approach. Thank you! select * from Account where cast(Replace(AccountDetails, 'UTF-8', 'utf-16') as xml).exist('/Account[Id=200]')=1

Collectives™ on Stack Overflow

Querying XML in SQL Server

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related