Tag: xml

Programmatically create stored procedure

I have a meta table and I am creating stored procedures using T-SQL string concatenation. declare @sql varchar(max) = ‘select ‘; select @sql += … from meta — select clause select @sql += … from meta — from clause …. It’s hard to maintain when the stored procedures get complex. Is it better to declare […]

Build Xml document from SQL Query using For Xml clause

I’m using AdventureWorks Database and I’d like to build xml block using query with FOR XML clause but i need to specify specific XML Schema in the result to have its datatypes and so on.. e.g. this sample query ” copied “ SELECT e.EmployeeID, c.FirstName, c.MiddleName, c.LastName FROM HumanResources.Employee e INNER JOIN Person.Contact c ON […]

Trouble with XSD, XML to SQL Import – Text node is not allowed in this location

I am creating my first XSD, as I have a 4MB XML file I need to parse into SQL, and processing something that big takes far too long using untyped XML (I gave up and cancelled the query after an hour). The XML file I have is in the following format (there are more elements […]

SQl Server T-SQL XML with Optional Elements

I have a table with a column of XML where some of the elements are optional. In this (extremely) simplified example, the code works when all elements are present, but I get nothing if one or more are absent. (i.e. zero rows instead of 1 with Y being null) I arrived at this technique through […]

SQL XML query from column

How to select file names from this kind of XML in a SQL Server column? file name is in attribute VALUE only if high level tag is object with name File like this <Object NAME=”File”> <Parameter ID=”1″ NAME=”Name” VALUE=”\\mysvr\fiels\readme1.txt” /> </Object> This sub-hierarchy can be placed on any level of xml (see example below) XML […]

Import XML by matching on tag attribute content in SQL Server 2005

I have an XML data file that looks like this: <Sales> <Store Number=”8″ Date=”2012-08-20T03:00:00″> <Value Field=”CanceledOrders” Value=”65.00″/> <Value Field=”TotalDollars” Value=”13.57″/> </Store> <Store Number=”10″ Date=”2012-08-20T03:00:00″> <Value Field=”CanceledOrders” Value=”38.00″/> <Value Field=”TotalDollars” Value=”29.22″/> </Store> </Sales> I am scratching my head on how to extract this into a normal SQL table like this: +——–+———————+—————-+————–+ | Number | Date | […]

Computed columns in view's nested queries

I have a table with nvarchar(max) column that may contain xml of known structure. I’d like to parse it to perform some aggregations so need to filter out “bad” entries. Here is the test case: create table TestTable ([Message] nvarchar(max)) insert into TestTable ([Message]) values (‘<root m=”1″/>’), (‘<root m=”7″/>’), (‘<rooo’) go set quoted_identifier on go […]

How do I set the xmlns attribute on the root element in the generated XML by using T-SQL's xml data type method: query?

I’ve created a simplified version of my problem: DECLARE @X XML = ‘<Root xmlns=”TestNS” xmlns:i=”http://www.w3.org/2001/XMLSchema-instance”> <Test> <Id>1</Id> <InnerCollection> <InnerItem> <Value>1</Value> </InnerItem> <InnerItem> <Value>2</Value> </InnerItem> <InnerItem> <Value>3</Value> </InnerItem> </InnerCollection> </Test> <Test> <Id>2</Id> <InnerCollection> <InnerItem> <Value>5</Value> </InnerItem> <InnerItem> <Value>6</Value> </InnerItem> <InnerItem> <Value>7</Value> </InnerItem> </InnerCollection> </Test> </Root>’ I’m trying to write a query that takes each <Test> element […]

XML to SQL temp tables

I have a XML string that looks like <PLATFORM> <PLATFORMNAME>UNIX</PLATFORMNAME> <OS>Ipad</OS> <VERSION>5.1</VERSION> <PLATFORMNAME>Windows</PLATFORMNAME> <OS>Windows</OS> <VERSION>2008 (64bit)</VERSION> </PLATFORM> Which I need to insert into a temptable Create Table #TempXMLTable ( PlatformName NVARCHAR(50), OS NVARCHAR(50), PlatformVersion NVARCHAR(50) ) INSERT INTO #TempXMLTable VALUES( @XML.value(‘(/PLATFORM/PLATFORMNAME)[1]’, ‘nvarchar(MAX)’), @XML.value(‘(/PLATFORM/OS)[1]’, ‘nvarchar(MAX)’) , @XML.value(‘(/PLATFORM/VERSION)[1]’, ‘nvarchar(MAX)’)) When I do the above insert only the […]

Load XML data into SQL Server, special XML structure

I’m trying to load XML data into a SQL Server table. The XML file is: <root> <Company ID=”183″> <User UserName=”Kim” /> <User UserName = “Joe” /> </Company> <Company ID=”123″> <User UserName=”George” /> <User UserName = “Sal” /> </Company> </root> I would like to get a table of UserName that has both the company ID and […]

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