Tag: xml

Parse XML with multilevel nesting in SQL

I’m trying to Parse some XML having multiple nesting levels in SQL. The problem I hit into here is how to write my query generic enough for it to Parse the XML below without having to hard-code the node path e:g EXEC sp_xml_preparedocument @handle OUTPUT, @xml INSERT #Root(ID) SELECT * FROM OPENXML(@handle, ‘/Root’) WITH (ID […]

How do you get a node that contains a child node with a given attribute value in an XML document using sql server?

I’m working on parsing XML documents using SQL Server 2008. I’m a complete noob and I was wondering if I can get help from you guys. I have an XML document like the one below and I want to get the “section” node where the “code” node has val=5. <root> <section> <code val=6 /> … […]

XQuery retrieval of a value

I have the following T-SQL that determines if a row exists using two criteria: Declare @x xml = ‘ <row ParentID=”45″ ObjectID=”0″ Node=”root.local.navigation[7]” itemKey=”page” itemValue=”Confirmation” itemType=”string” /> <row ParentID=”45″ ObjectID=”0″ Node=”root.local.navigation[7]” itemKey=”visited” itemValue=”false” itemType=”bool” />’ SELECT @x.exist(‘/row[@Node eq “root.local.navigation[7]”] and /row[@itemValue eq “Confirmation”]’) Question: Given the above SELECT, how can I SELECT the second row’s […]

Format XML Returned from SQL Server

I have a program that I have written in Visual Studio. Here is the code: namespace SQL_Connectivity { class Program { static void Main(string[] args) { SqlConnection conn = new SqlConnection(“Server=************.database.windows.net,0000;Database=testdb;User ID=testuser;Password= testpassword;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;”); conn.Open(); SqlCommand cmd = new SqlCommand(“SELECT * FROM District WHERE leaID <= 4 FOR XML PATH(‘districtEntry’), ROOT(‘districts’)”, conn); SqlDataReader reader = […]

Convert XML to table in SQL Server 2005

If I pass in an xml parameter to a stored proc which looks like this: <ClientKeys> <ck>3052</ck> <ck>3051</ck> <ck>3050</ck> <ck>3049</ck> … </ClientKeys> …and then convert the XML to a temp table like this: CREATE TABLE #ClientKeys ( ClientKey varchar(36) ) INSERT INTO #ClientKeys (ClientKey) SELECT ParamValues.ck.value(‘.’,’VARCHAR(36)’) FROM @ClientKeys.nodes(‘/ClientKeys/ck’) as ParamValues(ck) …the temp tbl is populated […]

SQL Server XML file with multiple nodes named the same

I have this inner XML which I am passing across to a SQL Server stored procedure. As you can see, it contains multiple root nodes but additionally, it can also contain 1 to ‘n‘ number of LotResults child nodes. Is there a way I can manipulate this in the stored procedure so that I can […]

Convert query/stored procedure result set to XML

I’m looking to take the result of a SQL Server stored procedure and transform it into XML in .NET/C# What I’m interested in is what standard libraries there are in .NET to help with the specifics of generating XML and if there any external libraries or tricks that can help automating the transformation from result […]

OpenXml function in stored procedure won't insert values in given order to table

I’m trying to insert a list of X,Y coordinates into a RideOfferCoordinates table but the stored procedure function inserts them always in the same order, regardless of the order they are sent in. I send the coordinates as an xml string: < Coordinates> < row X=”34.9116″ Y=”32.30498″/> < row X=”34.91151″ Y=”32.305420000000005″/> < row X=”34.85826″ Y=”32.328250000000004″/> […]

FOR XML multiple control by attribute in tree concept

I want to figure out one issue. I already had question about simple ordering issue but I want to order more detail. check below this link : SQL Server : FOR XML sorting control by attribute I made a example case. SQL Query. select ( select ‘123’ AS ‘@id’, ( select ( select ‘test’ AS […]

MS SQL Server 2008R2 : How to retrieve the content of a large text column?

I had a table with a column named xml_cache, containing large number of characters up to 80,000. The column is declared as nvarchar(max). I had problem retrieving the content of this column using SQL Management Studio SELECT [xml_cache], * FROM [dbo].[NZF_topic] AS nt WHERE nt.id LIKE ‘%nzf_1609%’ Wwhen I ran this SQL, the output grid […]

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