Tag: xml

SQL string manipulation to add xml nodes

I am using sql 2008R2, The table is having xml column like- <New> <From> <Scale>Tony</Scale> <ScaleName>Name</ScaleName> </From> </New> <New> <From> <Scale>Tom</Scale> <ScaleName>Name</ScaleName> </From> </New> <New> <From> <Scale>Seven</Scale> <ScaleName>Height</ScaleName> </From> </New> <New> <From> <Scale>Ten</Scale> <ScaleName>Height</ScaleName> </From> </New> <New> <From> <Scale>***XXX***</Scale> <ScaleName>Height</ScaleName> </From> </New> …….so on I need to write a SQL which can check all the […]

Swapping data between two XML files in sql script?

I have a column in one of my data base tables that holds xml files. However, these files have tags inside them that need to be swapped, is there a way to automate this with a sql script? <ViewcenterLayout> <viewcenter_config> <gl_type>EStop</gl_type> <data_access> <access_as>INPUT</access_as> <access_id>1391</access_id> <parent_id>0</parent_id> <server_id>17</server_id> <subsystem>0</subsystem> </data_access> <data> <dimension x=”1″ y=”1″ z=”1″ /> <curve_info […]

SQL Server 2008 XML Empty Node

I am trying to get some data returned in XML format from a 2005 and 2008R2 SQL Servers. I have two tables – a master and a detail. I want the XML to give the master information and within that the detail information (if it exists) for each master record queried. If no detail information […]

SQL Server not using XML Indexes for querying XML rowset

Hopefully someone can help with this. We have the need to return a data field from a rowset XML column in SQL. The routine works but does not use any of the created XML indexes (Primary + Secondary ones on Value/Path/Property but no Selective XML indexes). Due to the amount of XML the constant shredding […]

BizTalk WCF SQL Adapter: How to receive records from a database with one-to-many relationships

I have seen many tutorials going over how to use the WCF SQL adapter in a BizTalk receive port to pull in data from a SQL Server database. However, I have been unable to find any resources on how best to handle this same kind of operation when the data you are working with has […]

FOR XML PATH results in SSMS truncated to 2034 characters

I’m using SQL Server 2008 R2. I’m concatenating strings together using “for xml path(”)”. I have set the Tools->Options->SQL Server->Results to Grid options to max. I have set the Tools->Options->SQL Server->Results to Text options to max. Executing the query in Grid mode and copying the one row/one colum results, I see the return value is […]

Changing hard coded path to variable in MS SQL

Im using the following code and all works correctly DECLARE @Directory varchar(100) SELECT @Directory = ‘c:\XML\’ DECLARE @FileExist int DECLARE @FileName varchar(500),@DeleteCommand varchar(1000),@FullFileName varchar(500), @SQLFullFileName varchar(500) DECLARE @X XML SELECT @X = CONVERT(xml,[ICECAT-interface],2) FROM OPENROWSET(BULK ‘C:\XML\1382.xml’,SINGLE_BLOB) AS Import([ICECAT-interface]) select P1.X.value(‘@ID’, ‘int’) as ProductID, P2.X.value(‘@ID’, ‘int’) as ProductID from @X.nodes(‘/ICECAT-interface/Product’) as P1(X) cross apply P1.X.nodes(‘ProductRelated’) as […]

Moving database XML column data to new related tables

Basically I have two tables, Teachertbl with primary key of TeacherID, and TeacherBlobtbl with TeacherID relating to TeacherTbl, as well as an XML column TeacherBLOB. The TeacherBLOB XML data holds teacher data such as their class and students. The schema for the tables look like the following: TeacherTbl: TeacherID int PRIMARY KEY — Other columns […]

Identifying new tags between 2 XML in SQL

I’m using SQL to find the differences between 2 XMLS, for example, if i declare XML1 and XML2 as: <opr_tmp_manifest man_status=”2″ man_number=”5″/> <opr_tmp_manifest man_status=”1″ man_number=”5″/> Then the response I get is: “MAN_STATUS,2,1;” So the Format of the response is “name_of_tag,old_value,new_value” Another example with 2 differences, if given: <opr_tmp_manifest man_status=”2″ man_name=”Bob” man_number=”5″/> <opr_tmp_manifest man_status=”1″ man_name=”John” man_number=”5″/> […]

How to create XML nodes with attributes from Table

I have following example table Code here CREATE TABLE XMLData ( NodeName NVARCHAR(255), AttributA NVARCHAR(255), AttributB NVARCHAR(255), AttributC NVARCHAR(255), ) INSERT INTO XMLData VALUES (‘RowA’,”,’abcd’,’efgh’), (‘RowB’,’wxyz’,NULL,NULL), (‘RowC’,NULL,’qwer’,’tyui’), (‘RowD’,’stuv’,’erty’,’fghj’) SELECT * FROM dbo.XMLData How can I get following XML ? <NodeA> <NodeB /> <NodeC AttributeX=””> <RowA AttributeA=”” AttributeB=”abcd” AttributeC=”efgh” /> <RowB AttributeA=”wxyz” /> <RowC AttributeB=”qwer” AttributeC=”tyui” […]

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