Tag: xml

Get the value of each XML node and set it to a parameter

How to set a parameter in each XML nodes using SQL Server 2008? I am new with SQL and I have XML data like this Set @xml='<root><name>John </name><age>8</age></root>’ I need to set the value of node <name>, <age> in a parameter. For example: Set @name=’get value of node <name>’ Set @age=’the value of <age>’ Is […]

Insert XML file into SQL Server

I’m trying to import an XML file into a SQL table. I found a few examples of code to do this, but I can’t seem to get it to work. I’ve tried a few variations in my code but at this point I’m not sure if the issue is the XML file structure or my […]

What is an elegant way to use a column of sql output for the tag of xml output within mssql?

I have a stored procedure that returns xml. Sample call is get_organization_user_export_data_permission_procedure @organizationId = ‘64483342-af49-4a04-b25d-ce7346cb5375’ , @userId = ‘5324B48E-B560-4345-B290-12CC72D722FA’ Sample output is <root> <all>false</all> <analytics>false</analytics> <none>true</none> </root> Which is correct. The base SQL is select lower([type]) name, value permission, count(permissionType) found from [GroupExportRestrictionTypeConstants_RestrictionType_view] export left join ( select permissionType from [OrganizationUserDataPermission] where organizationId = ‘64483342-af49-4a04-b25d-ce7346cb5375’ […]

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 […]

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