0

I want to query the variables and their values in a SSIS package and since the package file is a xml file I thought I import it to SQL Server and query it from there. But it seems that the file contains single quotes from some SQL queries and when I paste the content in both single and double quotation marks the content cannot fit inside as text. So what kind of xml document is this SSIS package if it is not a valid xml?

I.e. when I try do do something like this it will not work? Is my only solution to do a "find and replace" of the single quotes with '?

DECLARE @xmlDocument nvarchar(max); 
SET @xmlDocument = N'<ROOT>  
<Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">  
<Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00"/>  
<Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00"/>  
</Customers>  
<Customers CustomerID="XYZBB" ContactName="Steve"  
CompanyName="Company2">No Orders yet!  
</Customers>  
</ROOT>';
xhr489
  • 1,957
  • 13
  • 39
  • 1
    Your xml file contains attributes and inner text. The attributes have the equal sign and the double quotes. – jdweng Sep 19 '20 at 23:16
  • @jdweng: but what ahould I do? – xhr489 Sep 20 '20 at 06:57
  • 1
    See https://learn.microsoft.com/en-us/sql/t-sql/xml/value-method-xml-data-type?view=sql-server-ver15#a-using-the-value-method-against-an-xml-type-variable – jdweng Sep 20 '20 at 09:25
  • @jdweng thanks I have posted a new question with sample data can you look at it? [link](https://stackoverflow.com/questions/63977818/query-xml-data-in-sql-sever-sample-data-from-microsoft-not-working) – xhr489 Sep 20 '20 at 10:14

0 Answers0