Tag: xml

How to use SQL variable to iterate XML nodes

I have this XML in SQL Server 2008: DECLARE @xml xml = ‘<Root> <Contacts> <Contact name=”John Doe” type=”REG” other=”value” /> <Contact name=”Jane Doe” type=”REG” other=”value” /> <Contact name=”Jennifer Doe” type=”REG” other=”value” /> <Contact name=”Jane Doe” type=”REG” other=”value” /> </Contacts> </Root>’ I want to change the value of the types to something else. I can hard […]

Passing irregular xml file to the stored procedure

I have a sample xml as follows. I am trying to get all or specific data and then insert into the my sql table which has the same columns representing the values coming from xml. I looked through some solutions but the xml files are not formatted like I have in here. Can you help […]

How can I avoid encoding when using “FOR XML PATH”?

Using FOR XML PATH encodes my HTML making my hyperlinks useless. I see that others are using the value method to prevent encoding. However, this strips out the table structure I need. I could do a search replace, effectively decoding the html. That does not seem like the right approach. The Company column in this […]

SQL: Select records that have XML tag

I would like to ask how to select only those records from a table that has a column with XML, that in their XML contain a specific tag. Table name is: Searchindex Column with XML: Record XML element: The tag is included only on a few records, not all. XML sample structure: <Record> <ppn>asasaa</ppn> <NAM>asdad</NAM> […]

Multiple goal search on XML nodes in SQL Server

I have a Process table in SQL Server like this: workflowXML column has values like this: sample1 (ProcessID=1) workflowXML of sample1: <process> <Event type=”start” id=”StartEvent_1″ name=”Start”> <outgoing>SequenceFlow_0z7u86p</outgoing> <outgoing>SequenceFlow_1onkt3z</outgoing> </Event> <task type=”” id=”Task_0a7vu1x” name=”D”> <incoming>SequenceFlow_108ajnm</incoming> <incoming>SequenceFlow_1onkt3z</incoming> <outgoing>SequenceFlow_01clcmz</outgoing> </task> <task type=”goal” id=”Task_00ijt4n” name=”B”> <incoming>SequenceFlow_17q1ecq</incoming> <incoming>SequenceFlow_0q9j3et</incoming> <outgoing>SequenceFlow_1ygvv8b</outgoing> <outgoing>SequenceFlow_02glv1g</outgoing> </task> <task type=”” id=”Task_1rnuz4y” name=”A”> <incoming>SequenceFlow_1ygvv8b</incoming> <incoming>SequenceFlow_0z7u86p</incoming> <outgoing>SequenceFlow_108ajnm</outgoing> <outgoing>SequenceFlow_17q1ecq</outgoing> <outgoing>SequenceFlow_075iuj9</outgoing> […]

XML serialization error in SQL Server when concatenating characters

I’m trying to concatenate a number of characters corresponding to some ints (the first 15 ASCII characters for example): ;with cte as ( select 1 nr union all select nr + 1 from cte where nr <= 15) select ( select char(nr) from cte for xml path (”), type).value(‘.’, ‘nvarchar(max)’) option (maxrecursion 0) but I’m […]

Skip NULL parameters in XML generated from SQL

What i’m trying to do?: generate XML from SQL Server 2014 Problem: some columns contain NULL – these columns act like attributes in my XML. My Validator does not accept an attribute if its value its empty/null. Example: number Type Trat Frecv ———– ———– ———- ———– 31301879 NULL 1 2 73229903 2 NULL 2 73229903 […]

Append unit to a XML column value in SQL Server

I have a table with a XML column for storing the file details. Now I want to update the size node in the XML column with KB or MB according to the value. Sample data: <FileInfo> <Field Name=”Filename”>PV_1_PV_4126_C-482N-25457-005_V1.pdf</Field> <Field Name=”Created”>02/21/2017</Field> <Field Name=”Modified”>02/21/2017</Field> <Field Name=”Uploaded By”>2120</Field> <Field Name=”Uploaded On”>02/21/2017</Field> <Field Name=”Size”>755</Field> </FileInfo> Expected result: <FileInfo> <Field […]

Updating xml with variables in SQL Server

Let’s say we have simple table T1 which has three columns: CREATE TABLE T1(C1 INT PRIMARY KEY, C2 VARCHAR(20), C3 XML) Now we create simple data: INSERT INTO T1 VALUES(1, ‘Test’, ‘<Element></Element>’) Then I want to modify third column to achieve something like this: <Element>Test</Element> Which means, C2 is inserted into XML. So I wanted […]

Are the elements of an XML alwas ordered if I get them from SQL Server 2012

I found “Order of Attributes” The order of attributes in an XML instance is not preserved. When you query the XML instance stored in the xml type column, the order of attributes in the resulting XML may be different from the original XML instance.” from a link https://msdn.microsoft.com/en-us/library/bb510442.aspx Q1) does that mean elements like following […]

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