Tag: xml

Why does this xml query in SQL Server not work

Lately I was blown by xml for SQL Server, but this one here gets me nuts DECLARE @xml XML; SET @xml = ‘ <ROOT> <Object> <FooProperty1>123</FooProperty1> <FooProperty2>456</FooProperty2> </Object> <Object> <FooProperty1>123</FooProperty1> <FooProperty2>456</FooProperty2> </Object> </ROOT> ‘; SELECT [doc].[FooProperty1].value(‘.’, ‘INT’) AS [fooProperty1], [doc].[FooProperty2].value(‘.’, ‘INT’) AS [fooProperty2] FROM @xml.nodes(‘/ROOT/Object’) AS [doc] ( [FooProperty1], [FooProperty2] ) Gives me Msg 8159, […]

SQL Server 2008: How to use SQL to output XML from Query?

I would like to declare a variable ‘XMLOutput’ and have it produce the contents of a table in XML format. If you could provide a really simple example I could work off of I would really appreciate it. I tried using the xmlelement() but could not get it to work.

SQL OpenXML Multiple Tag Issue

I am just new to reading in an XML file into a table through SQL Server Management Studio. There are probably better ways but I would like to use this approach. Currently I am reading in a standard XML file of records on people. A <record> tag is the highest level of each row of […]

ASP.NET Export Data To Word Template

I have several form letters in Word 2007 templates. I need to pass data from my web forms application(Sql Server 2008 backend). I need to do this as seamlessly as possible so generating a text file for word to use as a datasource is not an option. I also want to avoid running Office on […]

How do I select a top level attribute of an xml column in SQL Server 2005?

I have an xml column in SQL Server 2005 that is the equivalent of: <Test foo=”bar”> <Otherstuff baz=”belch” /> </Test> I want to be able to get the value of the foo attribute of Test (the root element) as a varchar. My goal would be something along the lines of: select cast( ‘<Test foo=”bar”><Otherstuff baz=”belch” […]

SQL Server to produce XML data rows from JOINed select statement

I have a three tables in SQL Server 2008 which are setup as follows: EMPLOYEE TABLE empid(PK) 1 2 joined to EMPLOYEEATTRIBUTES dataId(PK) | empId(FK) | attributeid | attributeVal 10 | 1 | A1 | somevalue1 20 | 1 | A2 | somevalue2 30 | 2 | A1 | somevalue3 40 | 2 | A3 […]

TSQL XML Query Question

Is this possible in TSQL? I am feeding this structure in via an xml param. And I need to set it into a temp table. DECLARE @xml xml SET @xml = ‘<Events> <Event id=”8″> <Responses> <Response id=”59″> <Loe> <Id>89</Id> </Loe> </Response> <Response id=”60″> <Loe> <Id>89</Id> <Id>90</Id> <Id>88</Id> <Id>87</Id> </Loe> </Response> </Responses> </Event> </Events>’; Trying to […]

SQL Query FOR XML runs fine in 2000, slow in 2008 R2

I’m converting a client’s SSIS packages from DTS to SSIS. In one of their packages they have an execute SQL task that has a query similar to this: SELECT * FROM [SOME_TABLE] AS ReturnValues ORDER BY IDNumber FOR XML AUTO, ELEMENTS This query seems to return in a decent amount of time on the old […]

How to choose returned column name in a SELECT FOR XML query?

MS SQL has a convenient workaround for concatenating a column value from multiple rows into one value: SELECT col1 FROM table1 WHERE col2 = ‘x’ ORDER by col3 FOR XML path(”) and that returns a nice recordset: XML_F52E2B61-18A1-11d1-B105-00805F49916B —————————————- <col1>Foo</col1><col1>Bar</col1> only the column name in the returned recordset is rather nasty! The column name seems […]

Modify xml with namespaces

I’m trying to modify predefined xml (xsd to be more specific) in TSQL. I want to insert enumeration restriction to one of the xsd elements. The task is to fill xsd restrictions based on a query example: create table #list(value nvarchar(100)) insert into #list values(‘item 1’) insert into #list values(‘item 2’) insert into #list values(‘item […]

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