Tag: xml

Referential integrity issue with Untyped XML in TSQL

I am going to start off by displaying my table structures: Numbers Table: Id AccountId MobileNr FirstName LastName AttributeKeyValues Labels — ———- ———– ———- ———– ——————- ——- 490 2000046 2XXXXXXXXXX Eon du Plessis <attrs /> <lbls> <lbl>Meep11e</lbl> <lbl>43210</lbl> <lbl>1234</lbl> <lbl>Label 5</lbl> <lbl>Label 6 (edit)</lbl> </lbls> —————————————————————————– Labels Table: Id AccountId Label RGB LastAssigned LastMessage ———– […]

Wrapping and removing CDATA around XML

This is my xml. My goal is to wrap the data inside the Value node with CDATA on an export and then import that back into an Xml type column with the CDATA removed. <Custom> <Table>Shape</Table> <Column>CustomScreen</Column> <Value>Data</Value> <Custom> Right now I am replacing ‘Data’ inside the Value node with the XML from the table […]

XML Dropdown with Names and Values

I have three dependent, cascading dropdown lists. They are populated from an XML file, which works fine for the list items, but I need the values included in the list items so I can pass them to the DB. Here is one node from my restructured XML document: <Organizations> <Organization> <name value=”A0EAD6A8813E474B9F9A3E0C64D72F61″>Hospital</name> <JobTitle1> <name value=”-2″>Physician</name> […]

iPhone Sdk can Access SQL Server Directly

This question already has an answer here: How do i query data from SQL Server 2005/2008 databases to iPhone 2 answers

SQL Server XML Parsing

I have a block of XML in a column of type xml in SQL Server database that looks like this: <p> <k>field</k> <v>user</v> </p> <p> <k>action</k> <v>delete+set</v> </p> <p> <k>prompt</k> <v>smith</v> </p> I have no control over how the XML is put into the database, I can only query it. I would like to write […]

SELECT from multiple tables without iteration

Trying to SELECT from Multiple tables into 1 query output. Only getting it to work with CROSS JOIN, the output though is not what i’m trying to achieve. It seems to iterate the query per COLUMN from the other table. SELECT @SQLCmd = + ‘bcp ‘ + ‘”SELECT ”<?xml version=””1.0″” encoding=””UTF-8″”?>” + ‘ + ‘ […]

SQL FOR XML multilevel from one pivoted table

I’ve been trying to use FOR XML without success to do the following. Source table: Country | ID | 1950 | 1955 —————————————————– Country 1 | 1 | 2.43 | 2.55 Country 2 | 2 | 4.54 | 42.15 Desired output: <locations> <location> <loc name=’Country 1′ id=’1′ /> <dub> <data year=’1950′ value=’2.43′ /> <data year=’1955′ […]

How to create a XSD which differs in validation on attribute value?

I have been searching for a while now, and came to the conclusion it may not be possible to change the validation per value of an attribute. For example I have two “action” nodes, both with a “type” attribute and two elements (“name” and “description”) Only when the value of the “type” attribute is “1” […]

TSQL Inserting records from XML string

I have a SQL query that is inserting records into a table from an XML string that I pass to it. The string could contain 1 node or multiple so each one is a new record. Here is my XML string: <root> <data> <segment> <trainingEventID>9</trainingEventID> <localeID>641</localeID> <numOfTeammates>12</numOfTeammates> <nonProdHrs>21</nonProdHrs> <segmentDate>10/10/2014</segmentDate> <trainers> <trainer> <empID>HUS123</empID> </trainer> <trainer> <empID>Dan123</empID> […]

How to export more than 1MB in XML format using sqlcmd and without an input file?

In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a “for xml path..” clause at the end. I read on BOL that if I don’t want my output truncated when reaching 1MB […]

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