Tag: xml

XML from webservice to SQL Server using SSIS

I have a web service task that saves the XML output to a variable. The question now is what would the variable type be? Also how can I then transfer the XML variable to the SQL Server database? I can’t seem to make the data flow work, XML source to ole db source? Am I […]

Tracking the flow of XML nodes in SQL Server

I have a Process table in SQL Server like this: workflowXML column has values like this: Sample1: <process> <Event type=”start” id=”StartEvent_1″> <outgoing>SequenceFlow_0h5l5vu</outgoing> </Event> <Flow type=”sequence” id=”SequenceFlow_0h5l5vu” sourceRef=”StartEvent_1″ targetRef=”Task_1qc93ha”/> <Flow type=”sequence” id=”SequenceFlow_120gi3p” sourceRef=”Task_1qc93ha” targetRef=”Task_0x1pjee”/> <Task type=”service” id=”Task_1qc93ha”> <incoming>SequenceFlow_0h5l5vu</incoming> <outgoing>SequenceFlow_120gi3p</outgoing> </Task> <Task type=”user” id=”Task_0x1pjee”> <incoming>SequenceFlow_120gi3p</incoming> </Task> </process> Sample2: <process id=”Process_1″ isExecutable=”false”> <Event type=”start” id=”StartEvent_142xowk”> <outgoing>SequenceFlow_03yocm5</outgoing> </Event> <Flow […]

XML Parsing & T-SQL

Given the following from an XML field in a table: <View> <Criminal xmlns=”http://tempuri.org/crimes.xsd”> <Person> <PersonID>1234</PersonID> <LastName>SMITH</LastName> <FirstName>KEVIN</FirstName> <Cases> <PersonID>1234</PersonID> <CaseNumber>12CASE34</CaseNumber> </Cases> </Person> </Criminal> </View> How would I pull the Person/PersonID, LastName, Firstname info? Same goes for the CaseNumber. My next issue is similar to above but lets add a second namespace: <MessageContent xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:xsd=”http://www.w3.org/2001/XMLSchema”> <Content>Content […]

Delete empty XML nodes using T-SQL FOR XML PATH

I’m using FOR XML PATH to construct XML out of a table in SQL Server 2008R2. The XML has to be constructed as follows: <Root> <OuterElement> <NumberNode>1</NumberNode> <FormattedNumberNode>0001</KFormattedNumberNode> <InnerContainerElement> <InnerNodeOne>0240</InnerNodeOne> <InnerNodeStartDate>201201</InnerNodeStartDate> </InnerContainerElement> </OuterElement> </Root> According to the schema files, the InnerContainerElement is optional, while the InnerNodeOne is required. The schema files aren’t set up by […]

SQL Server FOR XML – Basic query

I have been given an XML document that I want to generate via a SQL script, I’ve not done something like this and haven’t been able to find any examples that can lead me to being able to generate the final XML I need (and I’m not sure which of the possible methods available if […]

How to update a table based on an XML parameter

I have a table and I want to update one of its varchar fields based on the values in an XML parameter. I have the following table: ID Constraint_Value 1 (OldVal_1) (OldVal_2) 2 (OldVal_2) (OldVal_1) and I want to use the following XML to update the Constraint_Value field: <qaUpdates> <qaUpdate><old>OldVal_1</old><new>NewVal_1</new></qaUpdate> <qaUpdate><old>OldVal_2</old><new>NewVal_2</new></qaUpdate> </qaUpdates> After the update, […]

SQL Server – Storing linebreaks in XML data type

Is it possible to store non-alphanumeric characters (more specifically line break characters) in a XML data type? The code below illustrates my problem: declare @a xml declare @b nvarchar(max) set @b = ‘<Entry Attrib=”1’+CHAR(13)+’2″ />’ print @b set @a=convert(xml,@b,1) set @b=convert(nvarchar, @a,1) print @b The output is: <Entry Attrib=”1 2″ /> <Entry Attrib=”1 2″/> Is […]

add column value to an xml field as an attribute

I would like to add an attribute value to an xml field in a query. my example is below declare @table table (bookid int,xmlCol xml) insert into @table select 1, ‘<book title=”you are not alone” author=”Esther”> <EDITIONS> <edition year=”2012″/> <edition year=”2013″/> </EDITIONS> </book>’ declare @table1 table(bookid int,quantity int) insert into @table1 select 1,3 select ??? […]

add Prefix to XML tag in FOR XML AUTO mode

I’d like to know if it is possible to add TAG PREFIX before every ELEMENT TAG in the result of FOR XML AUTO mode. I know it’s possible with RAW mode but I need to make its as hierarchy as possible xml result Something like this: <prefix:attName> <prefix:ID>value</prefix:ID> <prefix:name>value</prefix:name> </prefix:attName> and the query in the […]

Get nested XML output sql server

I have a problem with my xml output from sql server database. My table: CREATE TABLE [dbo].[test_table]( [id] [int] IDENTITY(1,1) NOT NULL, [firstname] [nvarchar](255) NULL, [lastname] [nvarchar](255) NULL, [city] [nvarchar](255) NULL, [street] [nvarchar](255) NULL, [streetno] [int] NULL ) I want the output, where Address is nested inside each Person, like this: <Root> <Person id=”1″> <firstname>Stefanie</firstname> […]

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