Tag: xml

Converting a flat table into an XML that encodes parent child relations, but without heirarchical tags

I had an ask to convert a flat table into a particularly formatted XML which showed a parent child relationship, but had a flat structure of tags. I produced this SQL which grinds out the result, I’ve added some dummy data generation so it will run standalone. DECLARE @root varchar(250); SET @root = ‘AB&C’; DECLARE […]

Unable to Query XML file with OPENXML in SQL

I am trying to query a good number of XML files using SQL Server 2014. I am using the below code and I am unsure what is wrong with the syntax because nothing is being returned. My suspicion is there is something strange with the XML file. If put in only parts of the XML […]

Generating XML from SQL existing XSD file (complex)

I have an XSD file. I get data from SQL and fill data to dataset. note: I get 1000 records from SQL. I want to make this; generate XML file exisiting xsd format. Here is my XSD.: <xs:element name = ‘automation’> <xs:complexType> <xs:sequence> <xs:element name = ‘auto’ type = ‘AutoType’ minOccurs = ‘1’ maxOccurs = […]

How do you read an XML file in SQL?

I am using SQL Server 2014 to query XML text using xquery. I am able to insert portions of XML text to query but I need to be able to point to a local file to read and query it and cannot figure out how to do that. Below is what I have for what […]

SQL “For XML Path” – multiple nested results with null type

I will make a block code to a simple example of what i need select ( select (Select (Select NULLIF (t1.number,2) as ‘e’ FOR XML PATH(‘d’),type ) FOR XML PATH(‘c’),type ) FOR XML PATH(‘b1’), type ) , t1.number as ‘b2’ from (select 1 as number union all select 2 as number union all select 3 […]

values within ssms generated xml tags

I have the following code to generate an xml output. select [FirstName] as ‘SubscriberName/FirstName’, [Middle] as ‘SubscriberName/MiddleName’, LastName as ‘SubscriberName/LastName’, ‘123 fake street’ as ‘Address/Address1’ from chp_work_area.dbo.Students1099hc FOR XML PATH(‘SubscriberInformation’), ROOT(‘Test’) The output of this is the xml below, and is accurate. <Test> <SubscriberInformation> <SubscriberName> <FirstName>BOB</FirstName> <MiddleName>G</MiddleName> <LastName>CHOI</LastName> </SubscriberName> <Address> <Address1>123 fake street</Address1> </Address> </SubscriberInformation> […]

SQL Server 2012 query XML column

I have done a lot of researching on the net for the answer to this question. I have found a lot of help sites but I’m not getting the results unfortunately (I don’t really understand XML). I have a table (called CustomField.PersonCustomFieldValue) that has an XML datatype column (called XmlValue) which stores XML data. I […]

Return a column value if XML column contain a certain value

I have this table where we have 3 rows ID LOCATION PhoneNo XML 1 NY 123 xmlfile1 2 CA 322 xmlfile2 3 MA 151 xmlfile3 4 WA 111 xmlfile4 assume xmlfile1 and 2 looks similar <shop> <item> <Product>shirt</Product> <color>red</color> </item> <item> <Product>shirt</Product> <color>yellow</color> </item> <item> <Product>jeans</Product> <color>blue</color> </item> </shop> I do cross apply to find […]

Querying XML Data stored in SQL Server

I have a column called Resume of type XML stored in a table in MS-SQL Server. I want to retrieve all the candidates who are from the city Saginaw. When I use the query SELECT Resume.query(‘(: explicit namespace :)declare namespace ns=”Namespace-Resume”; //ns:Address/ns:Addr.Location/ns:Location/ns:Loc.City’) FROM JobCandidate I get all the values for the city which is fine […]

SSIS/SQL Server XML

I’m working creating a XML output from SQL Server 2008R2. Below the structure of the xml I want. (I will try to be clear as much as possible, but if you need more information, please let me know) Issue I’m having is with the node “RecordId”. This field has to be a running sequence throughout […]

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