Tag: xml

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


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

trying to retrieve info from dtsx xml file

I’m trying to pull specific values out of the executable objects in dtsx. I want things like SQLStatementSource, ObjectName, and Connection. I can’t seem to do it. I’m using XMLDocument and GetElementsByTagName. Here is my whole code, With DTS:ObjectData, I can get something to return. If I used anything with SQLStatementSource in it, I get […]

Passing XML data to a stored procedure and getting return value from it c#?

I am trying to pass xml data as parameter to a stored procedure and getting return value from the stored procedure. But for some reason, the stored procedure is only returning me the data from the last row and I would like to know how to fix that problem. This is what I tried so […]

Create a new column with values from parsed xml data

I am using SQL Server 2008 and am relatively new to SQL. I have a table that contains a column with datatype xml. The XML in this column contains restaurant information for customer receipts: <order> <Check> <CheckHeader> <CheckNumber>279084</CheckNumber> <ServerNumber>186</ServerNumber> <ServerName>ServerName</ServerName> <CheckTotal>8.95</CheckTotal> </CheckHeader> </Check> After doing some research on here I learned how to parse out […]

Using XML.Modify how to insert a node UNDERNEATH an existing node

Using SQL and the function xml.modify is there anyway to add a new node underneath a specific node? Here is my XML <TESTCASESUMMARY> <TESTCASES> <TESTS id=”1″> <TEST1>1</TEST1> <TEST2>10</TEST2> <TEST3>10</TEST3> <TEST4>0</TEST4> <TEST5>0.75</TEST5> <TEST6>10</TEST6> <TEST7/> <TEST8>0.05</TEST8> <TEST9>0.05</TEST9> <TEST10> <TEST11>0.05</TEST11>B</TEST10> <TEST12/> <TEST13/> </TESTCASES> </TESTCASESUMMARY> I currently have SET @xml.modify(‘insert <TEST11>{sql:variable(“@TEST11”)}</TEST11> as first into (TESTCASESUMMARY/TESTCASES/TESTS[sql:variable(“@counter”)]/TEST10)[1]’) i need to get […]

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