Storing and Querying XML Data in SQL Server

Using Transact-SQL for XML

Using mode queries in TSQL for XML allows server to convert result set to related XML formats.Four modes are available for this process:

• RAW - converts each row and gives XML format related with each row, could get 'element' command
• AUTO - same as RAW format, however has less flexibility
• EXPLICIT - much more complexity in queries, PATH way is recommended
• PATH - more flexibility and simplicity,used instead of previous mode

For more practice you can test it on Management Studio:

Moreover, in nested SELECT statements each row can be summarized as XML data type, as following example.Each output in rows will be returned as separate XML.

Getting Started with XQuery

Working on XML is implemented by XQuery language, similar to SQL how database professionals working on databases.Includes several methods that help to query XML, like SQL these commands returns values,modifying on it, removes.

Methods of XQuery

Except nodes() method,others are using in implementing XML as input data, not as shredding it as output.Here are some samples:

Shredding XML

Convert XML file to relational database we need convert somehow, shredding XML process is realizing by 3 ways.Primarily, by stored procedure named 'preparedocument' creating a tree of XML structure,it commands that 'I have XML document,please read it', afterward OPENXML function reads related file, finally remove document from system by 'removedocument'.

Mentioned sp commands are tremendously impact database server from performance aspect, around 1/8 of system is used.

See you in next posts.
To watch my YouTube videopost, in Turkish only;