Tag: xml

Retrieve complex XML from SQL Server 2016

Given these tables: DECLARE @Documents TABLE ( document_id int, document_file varchar(200), description varchar(200), pages int, write_barcode bit ) DECLARE @Data TABLE ( id int, tax_id varchar(100), bo_lgl_name varchar(200), document_id int, keydata varchar(100), field_name varchar(100), value varchar(100) ) populated with this data: INSERT INTO @Data VALUES (1, ‘123456789’, ‘Fred Flintstone’, 269, Null, ‘LastName’, ‘Smith’) INSERT INTO […]

Convert table to XML with attributes using T-SQL

I am trying to convert the tables: tblCustomAttributeSourceSchema that contains the COLUMN DEFINITIONS and tblLabelAttributes that contains the VALUES for each of the columns For your convenience I created the tables in this sqlfiddle: http://www.sqlfiddle.com/#!6/b2fde/1 I would like to convert this to a table containing “LabelID” (of type INT – originally from tblLabelAttributes) and “XML_VALUE” […]

SQL Querying From XML

I’m trying to query an XML file, to extract user/license information. I seem to be stuck though. Below is my data set: <?xml version=”1.0″ encoding=”UTF-16″?> <Users> <User> <UserName>Elise</UserName> <IsConnected>1</IsConnected> <Modules> <Module> <KeyType>LOGISTICS-LTD</KeyType> <KeyDesc>Limited Logistics User</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> <InstallNo>0020534918</InstallNo> </Module> <Module> <KeyType>SAP-ADDONS</KeyType> <KeyDesc>SAP AddOns</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> <InstallNo>0020534918</InstallNo> </Module> <Module> <KeyType>SAP0000007050</KeyType> <KeyDesc>Workflow</KeyDesc> <DbType>MSS</DbType> <BitmaskOfLicensedModules>-255</BitmaskOfLicensedModules> <ReferingCount>0</ReferingCount> […]

SQL Server variable in XML

I have code like this: declare @Temp TABLE (text varchar(max)); DECLARE @xml XML = N'<tt> <cpost s=”a” cena=”0.0000″ cpost_id=”16385″ flprt=”1″ moq=”0″ valuta_id=”2″ nmatr_id=”14117″ norg_id=”1791″ /> <cpost s=”a” norg_id=”3883″ nmatr_id=”14117″ valuta_id=”2″ moq=”0″ flprt=”0″ cpost_id=”9269″ cena=”0.0000″ /> <gx2tov s=”a” gx_id=”1″ gx2tov_id=”12435″ nmatr_id=”14117″ /> <kodp s=”a” kodp_id=”16496″ nmatr_id=”14117″ norg_id=”1847″ primtxt=”дали на ДСК как 607194 (7863)” /> <Sertif1 s=”a” […]

XML Values to retrieve in single Column with Any(,) Delimiter

My Xml file <Detials xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance”> <Tests> <Test Name=”Test1″ TotalMarks=”100″>95</Test> <Test Name=”Test2″ TotalMarks=”200″>65</Test> <Test Name=”Test3″ TotalMarks=”150″>95</Test> <Test Name=”Test4″ TotalMarks=”150″></Test> </Tests> </Detials> I want to query it back to view as TestName |TotalMarks |ScoreObtained** Test1,Test2,Test3,Test4|100,200,150,150| 95,65,95,| I tried with this ,But not able to get the Result above as i want,I got the error as XQuery [T1.XMLData.query()]: […]

Can't manage to export multiple rows from database to XML

I have a table with 3 rows, 1 is being used as row identifier, 2nd is used to make a relation with other table, and 3rd holds content. I’m doing a query where the the given result is kind of what I need but has a little mistake. Table: Here’s the query that I’m using: […]

How do I suppress empty namespaces with FOR XML in Sql Server

We are encountering a strange problem with SQL Server 2005/2008 using the FOR XML with fragments of xml and namespaces. Here is the query in question. WITH XMLNAMESPACES ( DEFAULT ‘http://tempuri.org/newincomingxml.xsd’, ‘http://tempuri.org/newincomingxml.xsd’ as [xsi], ‘http://tempuri.org/newincomingxml.xsd’ as [a] ) SELECT [@a:Source], [AddressCount], [ConsumerCount], [EmailCount], [PermissionCount] , ( SELECT [Consumer] FROM tbcExportBRC_Current xmlmaster FOR XML PATH(”), ROOT(‘Consumers’), […]

I am having trouble getting the correct results using XML EXPLICIT in T-SQL (SQL Server 2008)

I am getting strange results from a query using XML EXPLICIT mode in T-SQL (SQL Server 2008). Can someone explain what I am doing wrong? Here is my example: declare @parents table(id int, connection int, title nvarchar(255)); declare @children table(id int, connection int, title nvarchar(255)); insert into @parents(id, connection, title) values(1, 21, ‘1’); insert into […]

FOR XML EXPLICIT

Say I have this setup: — tables declare @main table (id int, name varchar(20)) declare @subA table (id int, mid int, name varchar(20)) declare @subA1 table (id int, subAid int, name varchar(20)) declare @subA2 table (id int, subAid int, name varchar(20)) declare @subB table (id int, mid int, name varchar(20)) — sample data insert @main […]

sql server bcp xml data

I have a table which has a column which is of type xml. I have to extract data from this table and load the data into another environment. i am using bcp to extract and laod the target table but there are some special characters that is causing some issues when i bcp them into […]

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