Tag: xml

Best way to search in data stored as XML in SQL Server?

This question already has an answer here: Use a LIKE statement on SQL Server XML Datatype 4 answers

SQL Server : FOR XML sorting control by attribute

I am generating a XML file from a SQL Server query. I have some problem about sorting elements issues. For example, there is simple code as below. <test> <tree abc=”123″/> <tree abc=”789″/> <tree-order abc=”456″/> </test> As you can see tree and tree-order are different elements and I want to sort by attributes as <test> <tree […]

Design database based on EAV or XML for objects with variable features in SQL Server?

I want to make a database that can store any king of objects and for each classes of objects different features. Giving some of the questions i asked on different forums the solution is http://en.wikipedia.org/wiki/Entity-attribute-value_model or http://en.wikipedia.org/wiki/Xml with some kind of validation before storage. Can you please give me an alternative to the ones above […]

XML Parsing with SQL query

I am trying to parse the below xml in sql server to get all the 3 Ids <Configuration> <ID>1000</ID> <ID>1001</ID> <ID>1002</ID> </Configuration> using the query SELECT CONFIGURATION.value(‘/’, ‘varchar(200)’) as SID FROM SCHEDULE am getting the results as 100010011002 but i would like to have the results in a column or in a CSV format. any […]

What is the performance penalty of XML data type in SQL Server when compared to NVARCHAR(MAX)?

I have a database that is going to keep log entries. One of the columns in the log table contains serialized (to XML) objects and a guy on my team proposed to go with XML data type rather than NVARCHAR(MAX). This table will have logs kept “forever” (archiving some very old entries may be considered […]

Parsing nested XML into SQL table

What would be the right way to parse the following XML block into SQL Server table according to desired layout (below)? Is it possible to do it with a single SELECT statement, without UNION or a loop? Any takers? Thanks in advance. Input XML: <ObjectData> <Parameter1>some value</Parameter1> <Parameter2>other value</Parameter2> <Dates> <dateTime>2011-02-01T00:00:00</dateTime> <dateTime>2011-03-01T00:00:00</dateTime> <dateTime>2011-04-01T00:00:00</dateTime> </Dates> <Values> […]

Does the `nodes()` method keep the document order?

Does the nodes() method of the xml data type return nodes in document order? For example, if there are data like: declare @xml xml set @xml = ‘<Fruits><Apple /><Banana /><Orange /><Pear /></Fruits>’ which is queried as select T.c.query(‘.’) from @xml.nodes(‘/Fruits/*’) T(c) will elements be returned in document order? Order of rows returned by select is […]

How to build a database from an XSD schema and import XML data

I have a complex XSD schema and hundreds of XML files conforming to the schema. How do I automate the creation of related SQL Server tables to store the XML data? I’ve considered creating C# classes from the XSD schema using the xsd.exe tool and letting something like Subsonic figure out how to make a […]

how to query SQL Server via REST to get XML

We have been using a web application framework to build apps that need to be able to query a SQL Server database and get the results as XML. In the past, the framework provided that capability. But that capability is now deprecated. So we were thinking, the framework allows us to easily query a REST […]

INSERT XML into SQL Server 2008 database

Hello I’m trying to insert some XML data into a table on SQL Server 2008. However I keep getting thrown this error; XML parsing: line 1, character 39, unable to switch the encoding The database column filemeta uses the XML datatype, and I’ve switch the encoding to UTF-16 which I believe is necessary for adding […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.