Tag: xml

SQL Server 2005 XML to table

I’m using classic asp and I’m passing in a varchar (ado data type) into an xml data column in a proc. The xml looks as follows: <DocumentCriteria> <Document documentGUID=”{B49654E7-9AF2-4B89-AF8F-5146F7CD4911}” graderFYC=”5907″> <criterion cnumber=”1″ rank=”3″/> <criterion cnumber=”3″ rank=”3″/> </Document> </DocumentCriteria>’ I’d like to have a table that looks like this: DocumentGUID|GraderFYC|Cnumber|Rank {guid} | 5907 | 1 | […]

Convert tabular data to XML using SQL Server

I have a flattened table that contains columns that represent groups that need to be displayed in XML. Example data: Market, Label, Style, Type XXX, YYY, JJJ, 111 XXX, YYY, JJJ, 222 XXX, YYY, JJJ, 333 XXX, YYY, JJJ, 444 XXX, YYY, LLL, 111 XXX, YYY, LLL, 222 XXX, YYY, LLL, 333 XXX, YYY, LLL, […]

Read value of XML attribute in SQL Server 2005

I have one table contains field named source with varchar(max). That field has following value <OutPatientMedication DateFormat=”MM-dd-yyyy” MedicationName=”lisinopril 10 mg oral tablet” Instructions=”2 cap(s) orally once a day ” Status=”Active” Quantity=”0″ Refills=”0″ PrescriptionType=”E”> </OutPatientMedication> Now I want to fetch value of Instructions attribute. How can I fetch value? Prompt reply will be appreciated. Thanks, Dhruval […]

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

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