Tag: xml

SQL XML, is there a technique by which I can get some columns in xml format?

I want to store data from multiple tables into one table table A ( p, q, PRIMARY KEY (p,q) ) table B ( p, m, n, PRIMARY KEY (p,m,n) ) table output( p, xml ) The output should be like for table A ——————————- | p | xml | ——————————— |value of p | <q>some […]

How to extract specific value from XML in SQL?

<Term xmlns:xxxlocal=”xx.xx.xx.xx” href=”abcde?terms=1″ policyTerm=”1″> <EffectiveDate>2012-05-22T00:00:00-04:00</EffectiveDate> <ExpirationDate>2013-05-22T00:00:00-04:00</ExpirationDate> <LocationCode>XXXXXX</LocationCode> <ProductRef href=”products/XXXXXXXXXXXXXXXXXXXXX” idref=”XXXXXXXXXXXXXXXXXXXXX” version=”ZZZZZZZZZZZZZZZZZZZZZZZ”> <CachedItem name=”Label” value=”I NEED TO EXTRACT THIS VALUE” /> </ProductRef> … I’m trying to extract the VALUE for the Label1 under <ProductRef>. I’m trying this but it is not working: SELECT XmlDoc.value(‘(/Term/ProductRef[1]/CachedItem[@name=sql:variable(“@Label”)]/@value)[1]’,’varchar(100)’) I keep getting the error: Msg 9501, Level 16, State 2, Line […]

Trying to get all xml nodes with SQL Server, what am I doing wrong?

I have the following xml in a table cell, in column MyColumn: <BSDL xmlns=”…” xmlns:i=”…”> <dateTime>2012-12-30T00:00:00Z</dateTime> <dateTime>2013-01-07T00:00:00Z</dateTime> <dateTime>2013-01-14T00:00:00Z</dateTime> <dateTime>2013-01-21T00:00:00Z</dateTime> <dateTime>2013-01-29T00:00:00Z</dateTime> <dateTime>2013-02-05T00:00:00Z</dateTime> <dateTime>2013-02-12T00:00:00Z</dateTime> <dateTime>2013-02-19T00:00:00Z</dateTime> <dateTime>2013-03-22T00:00:00Z</dateTime> <dateTime>2013-03-29T00:00:00Z</dateTime> <dateTime>2013-04-19T00:00:00Z</dateTime> </BSDL> I’m just trying to query it (get all xml nodes) using: SELECT BSDL.item.value(‘(dateTime)[1]’, ‘datetime’) from [MyTable] CROSS APPLY [MyColumn].nodes (‘//BSDL’) BSDL(item) it yields no result, although my MyColumn […]

How do I add xsi:nil=“true” to only one field while generating an xml file from SQL Server 2008 using for 'xml path'

Here is the code I am using: DECLARE @returnXML XML SELECT @ReturnXML = (SELECT [StudentID/SSN] = ssn, [StudentName/LastName] = lastname, [StudentName/FirstName] = firstname, [StudentName/MiddleInitial] = Middlename, [PrimaryAddress/Street1] = line1, [PrimaryAddress/Street2] = line2, [PrimaryAddress/City] = city, [PrimaryAddress/State] = state, [PrimaryAddress/Country] = ISNULL(country,null), [PrimaryAddress/Zip] = zipcode FROM StudentTable FOR XML Path(‘Student’), ROOT(‘Root’),TYPE ) SELECT @ReturnXML FOR XML […]

How to dump data from MSSQL to XML with SQL Agent?

I need to export data with SQL procedure from MSSQL (2012) to XML and run the script periodically with SQL Server Agent. I would like to do it directly from SQL Server, because now I am doing it with console application which is generating XML structure from raw data from SQL and it takes over […]

Store XSDs in a table versus XML SCHEMA COLLECTION

I’m doing some maintenance work on an application that stores its XML data in SQL Server. Currently, the application uses another table to store associated XSDs. My question: since all XSD validation is currently being performed in ASP.NET, is there any advantage to storing the XSDs in an XML SCHEMA COLLECTION instead?

Project relational data to XML column – is it possible?

I have 2 tables of the following structure: People Id | LastName | FirstName | Other columns… The second table has an XML column: Id | MyXmlCol | Other columns… MyXmlCol stores the following XML: <myData> <block> <person id=”1″ /> …other nodes </block> …other blocks </myData> The id attribute points actually to the Id column […]

Selecting a value from XML data type

How to write a SQL Server query that selects from an xml data type and selects attribute in an element that is matched based on other attribute. Imagine the following example : create table dbo.Configuration(Configuration xml not null); insert into dbo.Configuration(Configuration) values(convert(xml, ‘<?xml version=”1.0″ standalone=”yes”?> <Configuration> <DoSRequestAnalysis> <Windows> <Window Name=”Smallest” Duration=”15″> <ThresholdsToRemoveRequests NoofRequests=”25″ /> </Window> […]

How to import an XML file into SQL Server 2012

I have an XML file that I want to import into SQL Server 2012. I haven’t done it before and I don’t know a lot. After some research, I tried to open the xml file with the following command that was run in SQL Server Management Studio. SELECT CAST(x as XML) FROM OPENROWSET( BULK ‘C:\bulk\Users_test1111.xml’, […]

table Valued Function Query Optimize

Working on table Valued Function Query. Which is eating about 70% of the whole query execution time. Need some help in fine tuning it. INSERT INTO #XMLTAB SELECT ID,CAST(tab.tabxml as xml).value(‘(/Root/Element)[1]’, ‘varchar(100)’) FROM tab WHERE TabScore= 36 Ref : #XMLTAB is Temp table. Tab is table name in database. tabxml Column holds xml file tabscore […]

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